每日学习心得：SQL查询表的行列转换/小计/统计（with rollup，with cube，pivot解析）
1. SQL查询表的行列转换/小计/统计（with rollup，with cube，pivot解析）
在实际的项目开发中有很多项目都会有报表模块，今天就通过一个小的SQL查询统计来讲解一下实际开发中比较常用的行列转换/小计/统计等报表统计相关的常用知识点。
题目如下：
查询sales 和stores表，得出1993年每个store每季度销售数量及小计和总计，查询出的结果如下
其中sales表的数据结构如下：
其中stores表的数据结构如下：
1.1 普通方法（容易理解）
初看题目，第一感觉是竖表转横表，首先想到的是使用case when，
所以
第一步操作如下：
[csharp]
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
检索出结果如下：
这个时候由检索的结果可知，其中部分商店的统计信息没有合并统计，原因在于分组的时候我们是按商店名和日期分组的，
第二步操作，将第一步检索的信息，再次按店名分组统计，sql语句如下：
[csharp]
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一下就可以实现标准的结果
--对每个stor一年的销售明细进行汇总，之后按stor名分组
[csharp]
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.o