# 星星之火

::  ::  ::  ::  ::

## 1.    SQL查询表的行列转换/小计/统计（with  rollup，with cube，pivot解析）

查询sales 和stores表，得出1993年每个store每季度销售数量及小计和总计，查询出的结果如下

### 1.1 普通方法（容易理解）

select st.stor_name,SUM(sa.qty) as Total,
(case when datepart(qq,sa.ord_date)=1 then SUM(sa.qty) else 0 end) as Qtr1,
(case when datepart(qq,sa.ord_date)=2 then SUM(sa.qty) else 0 end) as Qtr2,
(case when datepart(qq,sa.ord_date)=3 then SUM(sa.qty) else 0 end) as Qtr3,
(case when datepart(qq,sa.ord_date)=4 then SUM(sa.qty) else 0 end) as Qtr4
from stores st left join sales sa
on st.stor_id=sa.stor_id
where DATEPART(yy,sa.ord_date)=1993
group by st.stor_name,sa.ord_date


select A.stor_name as stor_name ,SUM(A.Total) as Total,SUM(A.Qtr1) as Qtr1,
SUM(A.Qtr2) as Qtr2,SUM(A.Qtr3) as Qtr3,SUM(A.Qtr4) as Qtr4
from
(
--按时间和stor_name分组统计出对应的stor一年的销售明细
select st.stor_name,SUM(sa.qty) as Total,
(case when datepart(qq,sa.ord_date)=1 then SUM(sa.qty) else 0 end) as Qtr1,
(case when datepart(qq,sa.ord_date)=2 then SUM(sa.qty) else 0 end) as Qtr2,
(case when datepart(qq,sa.ord_date)=3 then SUM(sa.qty) else 0 end) as Qtr3,
(case when datepart(qq,sa.ord_date)=4 then SUM(sa.qty) else 0 end) as Qtr4
from stores st left join sales sa
on st.stor_id=sa.stor_id
where DATEPART(yy,sa.ord_date)=1993
group by st.stor_name,sa.ord_date) as A
group by A.stor_name


--对每个stor一年的销售明细进行汇总，之后按stor名分组

select A.stor_name as stor_name ,SUM(A.Total) as Total,SUM(A.Qtr1) as Qtr1,
SUM(A.Qtr2) as Qtr2,SUM(A.Qtr3) as Qtr3,SUM(A.Qtr4) as Qtr4
from
(
--按时间和stor_name分组统计出对应的stor一年的销售明细
select st.stor_name,SUM(sa.qty) as Total,
(case when datepart(qq,sa.ord_date)=1 then SUM(sa.qty) else 0 end) as Qtr1,
(case when datepart(qq,sa.ord_date)=2 then SUM(sa.qty) else 0 end) as Qtr2,
(case when datepart(qq,sa.ord_date)=3 then SUM(sa.qty) else 0 end) as Qtr3,
(case when datepart(qq,sa.ord_date)=4 then SUM(sa.qty) else 0 end) as Qtr4
from stores st left join sales sa
on st.stor_id=sa.stor_id
where DATEPART(yy,sa.ord_date)=1993
group by st.stor_name,sa.ord_date) as A
group by A.stor_name
union
--汇总统计信息
select 'Total',SUM(Total),SUM(Qtr1),SUM(Qtr2),SUM(Qtr3),SUM(Qtr4) from
(
--每个store一年的销售明细
select A.stor_name as stor_name ,SUM(A.Total) as Total,SUM(A.Qtr1) as Qtr1,
SUM(A.Qtr2) as Qtr2,SUM(A.Qtr3) as Qtr3,SUM(A.Qtr4) as Qtr4
from
(
select st.stor_name,SUM(sa.qty) as Total,
(case when datepart(qq,sa.ord_date)=1 then SUM(sa.qty) else 0 end) as Qtr1,
(case when datepart(qq,sa.ord_date)=2 then SUM(sa.qty) else 0 end) as Qtr2,
(case when datepart(qq,sa.ord_date)=3 then SUM(sa.qty) else 0 end) as Qtr3,
(case when datepart(qq,sa.ord_date)=4 then SUM(sa.qty) else 0 end) as Qtr4
from stores st left join sales sa
on st.stor_id=sa.stor_id
where DATEPART(yy,sa.ord_date)=1993
group by st.stor_name,sa.ord_date) as A
group by A.stor_name
) as B


### 1.2 With rollup  + case when count

SELECT ISNULL(stor_name,'Total') AS stor_name,SUM(qty) AS Total,
SUM(CASE WHEN DATEPART(qq,ord_date)=1 THEN qty ELSE 0 END) AS Qtr1,
SUM(CASE WHEN DATEPART(qq,ord_date)=2 THEN qty ELSE 0 END) AS Qtr2,
SUM(CASE WHEN DATEPART(qq,ord_date)=3 THEN qty ELSE 0 END) AS Qtr3,
SUM(CASE WHEN DATEPART(qq,ord_date)=4 THEN qty ELSE 0 END) AS Qtr4
FROM stores t INNER JOIN sales s ON s.stor_id = t.stor_id
WHERE YEAR(s.ord_date) = '1993'
GROUP BY stor_name WITH ROLLUP


### 1.3 With cube  +  povit

select isnull(t.stor_name, 'Total') as 'stor_name',
isnull(datepart(qq, ord_date),0) as 'Qtr', sum(qty) as 'qty'
from sales s
join stores t on s.stor_id = t.stor_id
where year(s.ord_date) = 1993
group by datepart(qq, ord_date), t.stor_name with cube


With cube语句跟with rollup语句作用很相像，它们的区别在于with CUBE 生成的结果集显示了所选列中值的所有组合的聚合，而with ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合

select stor_name, isnull([0],0) as 'Total',
isnull([1],0) as 'Qtr1',isnull([2],0) as 'Qtr2',
isnull([3],0) as 'Qtr3', isnull([4],0) as 'Qtr4'
from
(
select isnull(t.stor_name, 'Total') as 'stor_name',
isnull(datepart(qq, ord_date),0) as 'Qtr', sum(qty) as 'qty'
from sales s
join stores t on s.stor_id = t.stor_id
where year(s.ord_date) = 1993
group by datepart(qq, ord_date), t.stor_name with cube
) as tmp
pivot
(
sum(qty) for Qtr in ([0], [1], [2], [3], [4])
) as pvt


PIVOT用于行转列，在SQL Server 2000可以用聚合函数配合CASE语句实现，

PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

通过这样一个简单的查询，引出了今天要讲的表的行列转换（case when 和 pivot两种方法），表数据的统计（with rollup 和with cube方法），这也就达到了总结的目的。重要的不是讲这些方法怎么怎么用，主要是讲求解决问题的一个思路，以及在解决问题后对性能及效率的优化，希望可以对大家有些帮助。