SQL - 行列转换与统计（with rollup，with cube，pivot解析）

淡淡的烟草味﹌ 2022-10-16 10:27 217阅读 0赞

分享一个大牛的人工智能教程。零基础！通俗易懂！风趣幽默！希望你也加入到人工智能的队伍中来！请点击[http://www.captainbed.net][http_www.captainbed.net]

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

在实际的项目开发中有很多项目都会有报表模块，今天就通过一个小的SQL查询统计来讲解一下实际开发中比较常用的行列转换/小计/统计等报表统计相关的常用知识点。

题目如下：

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

![a0cccbed3c7fc17bd22ebf97e59d9e12.png][]

其中sales表的数据结构如下：

![9b75ba5843e5e8885c2304af0276e9d9.png][]

其中stores表的数据结构如下：

![4ef3762f079cbd8e1f6c6949b06016bc.png][]

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

初看题目，第一感觉是竖表转横表，首先想到的是使用case when，

所以

第一步操作如下：

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td> <code>select st.stor_name,SUM(sa.qty)&nbsp;</code><code>as</code>&nbsp;<code>Total,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>(</code>&nbsp;<code>case</code>&nbsp;<code>when datepart(qq,sa.ord_date)=1 then SUM(sa.qty)&nbsp;</code><code>else</code>&nbsp;<code>0 end)&nbsp;</code><code>as</code>&nbsp;<code>Qtr1,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>(</code>&nbsp;<code>case</code>&nbsp;<code>when datepart(qq,sa.ord_date)=2 then SUM(sa.qty)&nbsp;</code><code>else</code>&nbsp;<code>0 end)&nbsp;</code><code>as</code>&nbsp;<code>Qtr2,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>(</code>&nbsp;<code>case</code>&nbsp;<code>when datepart(qq,sa.ord_date)=3 then SUM(sa.qty)&nbsp;</code><code>else</code>&nbsp;<code>0 end)&nbsp;</code><code>as</code>&nbsp;<code>Qtr3,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>(</code>&nbsp;<code>case</code>&nbsp;<code>when datepart(qq,sa.ord_date)=4 then SUM(sa.qty)&nbsp;</code><code>else</code>&nbsp;<code>0 end)&nbsp;</code><code>as</code>&nbsp;<code>Qtr4</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>from stores st left join sales sa</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>on st.stor_id=sa.stor_id</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>where DATEPART(yy,sa.ord_date)=1993</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>group by st.stor_name,sa.ord_date</code> </td> 
 </tr> 
 </tbody> 
</table>

检索出结果如下：

![ef2571e351252c0508477055264d66d3.png][]

这个时候由检索的结果可知，其中部分商店的统计信息没有合并统计，原因在于分组的时候我们是按商店名和日期分组的，

第二步操作，将第一步检索的信息，再次按店名分组统计，sql语句如下：

[?][Link 1]

统计结果如下：

![4c4ca9ab68c53db526b97d07769c615f.png][]

这个时候已经很接近标准答案了，但是还有一个统计行需要统计列出

第三步，将第二步统计的结果再和总计的结果Union一下就可以实现标准的结果

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

[?][Link 1]

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

执行之后就可以得出我们想要的结果。

总结一下解题的整个思路，首先看题目要求求出每个店铺每年，每季度的销售统计，同时最后还要有总计行，统计全年/每个季度的销售总额。

接着通过case when语句查询出每个商店每年每季度的销售总统计，因为是按商店名和时间分组的，所以在查询出大体的数据结构之后，还需要再对结果进行按商店分组统计， 这样就统计出了符合答案要求的数据，最后在将统计出的结果与以结果为基础的再次统计union一下就得出了最终的答案。看起来很复杂的一个查询，只要把思 路理清之后一步一步实现就很容易了。

虽然我们经过查询实现了题目的要求，但是再让我们回过头来看看我们的查询语句，数据少的时候这样查询还没什么问题，但是如果数据量过大就会有很严重 的性能问题，同时，这样的sql查询语句过于庞大，有木有可以优化的方案呢？答案是肯定的。下面就给大家讲一下优化的查询解决方案。

### 1.2 With rollup  + case when count ###

首先我们的查询思路还是一下的，先用case when语句构建出大体的查询框架，唯一不同的是在group by 之后我们多了with rollup语句。代码如下：

[?][Link 1]

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

在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语句。代码如下：

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td> <code>select isnull(t.stor_name,&nbsp;</code><code>'Total'</code>&nbsp;<code>)&nbsp;</code><code>as</code>&nbsp;<code>'stor_name'</code>&nbsp;<code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>isnull(datepart(qq, ord_date),0)&nbsp;</code><code>as</code>&nbsp;<code>'Qtr'</code>&nbsp;<code>, sum(qty)&nbsp;</code><code>as</code>&nbsp;<code>'qty'</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>from sales s</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>join stores t on s.stor_id = t.stor_id</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>where year(s.ord_date) = 1993</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp;<code>group by datepart(qq, ord_date), t.stor_name with cube</code> </td> 
 </tr> 
 </tbody> 
</table>

执行结果如下：

![d46120533420cdccf0041924d5fe4102.png][]

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

第二步，我们将原始数据经过第一步的查询之后转换成了个标准的竖表，下边要做的就是如何将这个竖表转换成横表，我们在上边都是用case when的语法来实现这种表的横竖转换，这里我们换一种方式来实现。这里我们用povit方法来实现。代码如下：

[?][Link 1]

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

上边代码示例中高亮部分即为使用pivot进行表的横竖转换的关键代码。

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

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

这跟我们上边示例中使用的高亮标注的部分的方法是一样的

总结：

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

[http_www.captainbed.net]: http://www.captainbed.net/troubleshooter
[a0cccbed3c7fc17bd22ebf97e59d9e12.png]: /images/20221014/227069d397f04f40834fd3b85e5009fb.png
[9b75ba5843e5e8885c2304af0276e9d9.png]: /images/20221014/3e9bcdbd0bb242f08c9cfe790d434fb5.png
[4ef3762f079cbd8e1f6c6949b06016bc.png]: /images/20221014/88d82c40fb83446fb08acbb1788ad523.png
[Link 1]: http://www.cnblogs.com/Olive116/p/3271706.html
[ef2571e351252c0508477055264d66d3.png]: /images/20221014/01ee520f57b94fd68589288e24f4616e.png
[4c4ca9ab68c53db526b97d07769c615f.png]: /images/20221014/eb34b322465a4083bca33117df6a6ac0.png
[d46120533420cdccf0041924d5fe4102.png]: /images/20221014/ab570cc1852e482cb8c3706368c7507e.png