每日学习心得：SQL查询表的行列转换/小计/统计（with rollup，with cube，pivot解析）(二)
rd_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
执行之后就可以得出我们想要的结果。
总结一下解题的整个思路，首先看题目要求求出每个店铺每年，每季度的销售统计，同时最后还要有总计行，统计全年/每个季度的销售总额。
接着通过case when语句查询出每个商店每年每季度的销售总统计，因为是按商店名和时间分组的，所以在查询出大体的数据结构之后，还需要再对结果进行按商店分组统计，这样就统计出了符合答案要求的数据，最后在将统计出的结果与以结果为基础的再次统计union一下就得出了最终的答案。看起来很复杂的一个查询，只要把思路理清之后一步一步实现就很容易了。
虽然我们经过查询实现了题目的要求，但是再让我们回过头来看看我们的查询语句，数据少的时候这样查询还没什么问题，但是如果数据量过大就会有很严重的性能问题，同时，这样的sql查询语句过于庞大，有木有可以优化的方案呢？答案是肯定的。下面就给大家讲一下优化的查询解决方案。
1.2 With rollup + case when count
首先我们的查询思路还是一下的，先用case when语句构建出大体的查询框架，唯一不同的是在group by 之后我们多了with rollup语句。代码如下：
[csharp]
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
在group by 之后加上with rollup，我们执行一下查询语句，就会发现马上出现了我们想要的结果，这是为什么呢？
在生成包含小计和合计的报表时，ROLLUP 运算符很有用。GROUP BY子句允许一个将额外行添加到简略输出端 WITH ROLLUP 修饰符。这些行代表高层(或高聚集)简略操作。ROLLUP 因而允许你在多层分析的角度回答有关问询的问题。或者你可以使用 ROLLUP, 它能用一个问询提供双层分析。将一个 WITH ROLLUP修饰符添加到GROUP BY 语句，使询问产生另一行结果，也就是在上例中采用rollup之后，在按stor_name分组之后，还能检索出本组类的整体聚合信息。
如果有多重分组列的情况时，ROLLUP产生的效果更加复杂。这时，每次在除了最后一个分类列之外的任何列出现一个 “break” (值的改变) ，则问讯会产生一个高聚集累计行。
1.3 With cube + povit
上例中我们讲了使用with rullup来实现统计分组，那么还木有比with rollup 更加简便的查询呢？答案是肯定的。
首先我们想按照商店和时间分组统计出每家商店每年/季度的销售情况，这个时候我们需要借助于with cube语句。代码如下：
[csharp]
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 生成的结果集显示了所选列中值的某一层次结构的聚合
第二步，我们将原始数据经过第一步的查询之后转换成了个标准的竖表，下边要做的就是如何将这个竖表转换成横表，我们在上边都是用case when的语法来实现这种表的横竖转换，这里我们换一种方式来实现。这里我们用povit方法来实现。代码如下：
[csharp]
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
pivo