每日学习心得：SQL查询表的行列转换/小计/统计（with rollup，with cube，pivot解析）
每日学习心得：SQL查询表的行列转换/小计/统计（with rollup，with cube，pivot解析）
1. & &SQL查询表的行列转换/小计/统计（with &rollup，with cube，pivot解析）
在实际的项目开发中有很多项目都会有报表模块，今天就通过一个小的SQL查询统计来讲解一下实际开发中比较常用的行列转换/小计/统计等报表统计相关的常用知识点。
题目如下：
&查询sales 和stores表，得出1993年每个store每季度销售数量及小计和总计，查询出的结果如下
& & & & & & & & & & & &
其中sales表的数据结构如下：
其中stores表的数据结构如下：
1.1 普通方法（容易理解）
初看题目，第一感觉是竖表转横表，首先想到的是使用case when，
第一步操作如下：
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语句如下：
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名分组
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 &
--汇总统计信息 &
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 &
执行之后就可以得出我们想要的结果。
总结一下解题的整个思路，首先看题目要求求出每个店铺每年，每季度的销售统计，同时最后还要有总计行，统计全年/每个季度的销售总额。
接着通过case when语句查询出每个商店每年每季度的销售总统计，因为是按商店名和时间分组的，所以在查询出大体的数据结构之后，还需要再对结果进行按商店分组统计，这样就统计出了符合答案要求的数据，最后在将统计出的结果与以结果为基础的再次统计union一下就得出了最终的答案。看起来很复杂的一个查询，只要把思路理清之后一步一步实现就很容易了。
虽然我们经过查询实现了题目的要求，但是再让我们回过头来看看我们的查询语句，数据少的时候这样查询还没什么问题，但是如果数据量过大就会有很严重的性能问题，同时，这样的sql查询语句过于庞大，有木有可以优化的方案呢？答案是肯定的。下面就给大家讲一下优化的查询解决方案。
1.2 With rollup &+ case when count
首先我们的查询思路还是一下的，先用case when语句构建出大体的查询框架，唯一不同的是在group by 之后我们多了with rollup语句。代码如下：
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语句。代码如下：
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方法来实现。代码如下：
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' &
& & & & &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 &
& & & & &sum(qty) for Qtr in ([0], [1], [2], [3], [4]) &
) as pvt &
上边代码示例中高亮部分即为使用pivot进行表的横竖转换的关键代码。
PIVOT用于行转列，在SQL Server 2000可以用聚合函数配合CASE语句实现，
PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (&) )AS P
这跟我们上边示例中使用的高亮标注的部分的方法是一样的
& & &通过这样一个简单的查询，引出了今天要讲的表的行列转换（case when 和 pivot两种方法），表数据的统计（with rollup 和with cube方法），这也就达到了总结的目的。重要的不是讲这些方法怎么怎么用，主要是讲求解决问题的一个思路，以及在解决问题后对性能及效率的优化，希望可以对大家有些帮助。
您对本文章有什么意见或着疑问吗？请到您的关注和建议是我们前行的参考和动力&&
您的浏览器不支持嵌入式框架，或者当前配置为不显示嵌入式框架。后使用快捷导航没有帐号？
只需一步，快速开始
查看: 2116|回复: 1
UID263628帖子阅读权限20精华贴数技术排名76087 专家积分0 社区排名335172 技术积分24 社区积分0 注册时间
精华贴数专家积分0 技术积分24 社区积分0 注册时间
论坛徽章:0
组织结构设计问题？
将组织结构设计成树型结构，如：
& && &&&上海
& && &&&杭州
& && &&&南京
& && &&&…
& && &&&广州
& && &&&深圳
& && &&&厦门
& && &&&…
& && & 北京
& && & 济南
& && & 青岛
如何设计各节点编号？
我的解决方式：
华东 001 上海 001001 杭州 001002 南京 001003
华南 002 广州 002001 深圳 002002 厦门 002003
华北 003 北京 003001 济南 003002 青岛 003003
当节点发生调整后，节点编号如何调整？
将上海和北京调整到新节点（大城市）下，
那组织结构调整后如：
& && &&&杭州
& && &&&南京
& && &&&…
& && &&&广州
& && &&&深圳
& && &&&厦门
& && &&&…
& && & 济南
& && & 青岛
& & 大城市
& && &北京
那么，各节点编号如何变化？
我的解决方式：
华东 001 上海 001001 杭州 001002 南京 001003
华南 002 广州 002001 深圳 002002 厦门 002003
华北 003 北京 003001 济南 003002 青岛 003003
大城市 004 北京004001 上海004002
那么，如果是北京、上海下面都有子节点，这时发生调整了，各子节点也都得跟这调整，是不是会很麻烦，大家在设计的时候，都用了那些方法来改进这个问题？
疑问二是疑问一的延伸，也就是，如果组织结构调整了，那么原有的各组织节点所属的业务数据如何统计查询？
例如：2008年4月时，上海属于华东区，2008年5月时，上海调整到了大城市，那么，在4月时，统计华东区业务数据时，根据组织结构查询，是可以将上海的业务数据统计进来的，但是到了5月，再次统计华东区业务数据时，由于组织结构调整，上海区已经不归属于华东区，这时根据组织机构统计，就不会将上海5月以前的数据统计进来了，与4月统计的数据不符，大家有没有这种情况发生，你们的数据库是如何设计的，是如何来解决这个问题的？
请大家帮忙讨论，提供解决良策！
&&nbsp|&&nbsp&&nbsp|&&nbsp&&nbsp|&&nbsp&&nbsp
UID676041帖子阅读权限20精华贴数技术排名72314 专家积分0 社区排名734691 技术积分26 社区积分0 注册时间
精华贴数专家积分0 技术积分26 社区积分0 注册时间
论坛徽章:0
我建议您，模型要符合现实.不要奢望用什么编码方式一步到位，其实您还没有真正了解业务模型
每个城市是一个小结点，多个城市合成一个区域
把城市和区域建模成不同的实体
每个城市用不同的编号区分即可，此时是看不出区域来的
然后把区域和 城市编号的归属关系建模:一个区域含有0个或多个城市
表就是这样:
& && && &区域编号& && & 城市编号
& && && &1(华北)& && && &1(北京)
& && && &1& && && && && && &5（大连）
& && && &1& && && && && &&&3（青岛）
& && && &2（华东）& &&&2(上海）
& && && &2& && && && && &&&6（苏州）
& && && &3（华南）& &&&4（广州）
如果区域划分可能变化，则您还要将区域与城市对照的表加入时间
例如&&71231 对照组编号1
& && &&&~至今& && && &&&对照组编号2
统计销售时，依据时间划分即可找到不同对照组，然后归入不同区域
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号Oracle 查询问题 按照工作日和周末统计数据
id day riqi
希望查询结果为:
idcount 列为所有数据的count
工作日列统计 星期一到星期五的数据数
周末列 统计 周六、周日的数据数
求sql 语句
相关参考资料大量数据下单价*数量计算的sql查询速度如何优化？
[问题点数：60分，结帖人gzronald70]
大量数据下单价*数量计算的sql查询速度如何优化？
[问题点数：60分，结帖人gzronald70]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
相关帖子推荐：
2011年 总版技术专家分年内排行榜第四2010年 总版技术专家分年内排行榜第九2009年 总版技术专家分年内排行榜第八
2011年10月 总版技术专家分月排行榜第一
2011年8月 总版技术专家分月排行榜第三
2012年10月 荣获微软MVP称号2011年10月 荣获微软MVP称号
2011年8月 总版技术专家分月排行榜第三
2012年10月 荣获微软MVP称号2011年10月 荣获微软MVP称号
2011年12月 总版技术专家分月排行榜第三2011年9月 总版技术专家分月排行榜第三
2011年6月 MS-SQL Server大版内专家分月排行榜第一
2014年11月论坛优秀版主
2014年4月 荣获微软MVP称号2013年4月 荣获微软MVP称号2009年1月 荣获微软MVP称号2012年4月 荣获微软MVP称号2011年4月 荣获微软MVP称号2010年4月 荣获微软MVP称号
本帖子已过去太久远了，不再提供回复功能。}