关于SQL的分组汇总统计(关键字 Grouping) - 吴小贱就是矫情
在做数据分组查询的时候我们经常会用到分组汇总查询，做个简单的例子：
create table #bbb (大类 nvarchar(10),小类 nvarchar(10),酒水名称 nvarchar(20),数量 int,单价 Money) ----------------------------------------------------- insert into #bbb select \'酒水\',\'啤酒\',\'青岛\',10,5 insert into #bbb select \'酒水\',\'啤酒\',\'哈尔滨\',13,5 insert into #bbb select \'酒水\',\'啤酒\',\'燕京\',14,6 insert into #bbb select \'酒水\',\'啤酒\',\'青岛春生\',15,8 insert into #bbb select \'酒水\',\'白酒\',\'老白干\',18,8 insert into #bbb select \'酒水\',\'白酒\',\'二锅头\',30,5 insert into #bbb select \'酒水\',\'白酒\',\'邵大\',20,15 insert into #bbb select \'酒水\',\'白酒\',\'茅台\',8,200 insert into #bbb select \'酒水\',\'红酒\',\'长城干红\',90,30 insert into #bbb select \'酒水\',\'红酒\',\'长城甜红\',30,20 insert into #bbb select \'酒水\',\'红酒\',\'解百纳\',10,55 --------------------------------------------------------- insert into #bbb select \'香烟\',\'本地\',\'中南海\',10,5 insert into #bbb select \'香烟\',\'本地\',\'XXXOOO\',13,8 insert into #bbb select \'香烟\',\'国内\',\'白沙\',18,5 insert into #bbb select \'香烟\',\'国内\',\'芙蓉王\',30,25 insert into #bbb select \'香烟\',\'进口\',\'rrr\',90,30 insert into #bbb select \'香烟\',\'进口\',\'tttt\',30,20 --------------------------------------------------------- insert into #bbb select \'食品\',\'全胜\',\'全胜食品\',100,1 insert into #bbb select \'食品\',\'闵城\',\'XXX\',130,8 insert into #bbb select \'食品\',\'哇哈哈\',\'矿泉水\',208,1 insert into #bbb select \'食品\',\'百事\',\'百事可乐\',300,2.5 insert into #bbb select \'食品\',\'可口可乐\',\'可口可乐\',250,3 insert into #bbb select \'食品\',\'oooo\',\'tttt\',30,20 ------------------------------------------------------ select case when grouping([小类]) = 1 then [大类] else \'\' end as [大类], case when grouping([酒水名称]) = 1 then [小类] else \'\' end as [小类], [酒水名称], case when grouping([酒水名称]) = 1 then null else min([单价])end as[单价], sum([数量])as [数量], sum([数量]*[单价])as[总额] from #bbb group by [大类],[小类],[酒水名称] with rollup having grouping([大类]) = 0 order by grouping([大类])desc, case when grouping([大类]) = 0 then [大类] end desc, grouping([小类])desc, case when grouping([小类]) = 0 then [小类] end desc, grouping(酒水名称)desc, case when grouping(酒水名称) = 0 then 酒水名称 end desc drop table #bbb
执行结果如下：
版权声明：本文为demo8原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。