在做数据分组查询的时候我们经常会用到分组汇总查询，做个简单的例子：
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
执行结果如下：
