在客户端得到一个结果集,要求最后一行是合计,哪种方法最好。
--------------------------
求一个查询语句,目的是生成汇总统计结果,要求这个结果最后一行是合计行。
我不知哪种办法好,我现在是用存贮过程实现,在存贮过程中生成两个临时表,一个是汇总结果,另一个是合计行。
两个表的列完全相同,最后 select ... union all select ...
感觉这种办法比较笨,有没有好的办法呢?
今天看书,发现有 compute by 子句可以实现合计的统计,但是列数不一样,而且是两个结果集。不是一个结果集,求更好的解决方法。用union all 拼接两个临时表的办法确实有点太笨了。
--------------------------
求一个查询语句,目的是生成汇总统计结果,要求这个结果最后一行是合计行。
我不知哪种办法好,我现在是用存贮过程实现,在存贮过程中生成两个临时表,一个是汇总结果,另一个是合计行。
两个表的列完全相同,最后 select ... union all select ...
感觉这种办法比较笨,有没有好的办法呢?
今天看书,发现有 compute by 子句可以实现合计的统计,但是列数不一样,而且是两个结果集。不是一个结果集,求更好的解决方法。用union all 拼接两个临时表的办法确实有点太笨了。
--> 测试时间:2009-07-10
--> 我的淘宝:http://shop36766744.taobao.com/if object_id('[Inventory]') is not null drop table [Inventory]
create table [Inventory]([Item] varchar(5),[Color] varchar(4),[Quantity] int)
insert [Inventory]
select 'Table','Blue',124 union all
select 'Table','Red',223 union all
select 'Chair','Blue',101 union all
select 'Chair','Red',210--COMPUTE事例
select * from [Inventory] order by Item COMPUTE sum(Quantity) /*
Item Color Quantity
----- ----- -----------
Chair Blue 101
Chair Red 210
Table Blue 124
Table Red 223 sum
===========
658
(所影响的行数为 5 行)*/--COMPUTE by事例
select * from [Inventory] order by Item COMPUTE sum(Quantity) by Item
/*
Item Color Quantity
----- ----- -----------
Chair Blue 101
Chair Red 210 sum
===========
311
Item Color Quantity
----- ----- -----------
Table Blue 124
Table Red 223 sum
===========
347
(所影响的行数为 6 行)
*/--With rollup
select Item,Color,s=sum(Quantity) from Inventory group by Item,Color With rollup/*
Item Color s
----- ----- -----------
Chair Blue 101
Chair Red 210
Chair NULL 311
Table Blue 124
Table Red 223
Table NULL 347
NULL NULL 658(所影响的行数为 7 行)*/--with cube
select item,color,sum(Quantity) qtysum from Inventory group by item,color with cube/*
item color qtysum
----- ----- -----------
Chair Blue 101
Chair Red 210
Chair NULL 311
Table Blue 124
Table Red 223
Table NULL 347
NULL NULL 658
NULL Blue 225
NULL Red 433(所影响的行数为 9 行)*/drop table Inventory
create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7) insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)
情况一:只有一个分类汇总列时,只需要一个合计。只需要增加with rollup即可。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup
情况二:有多个分类汇总列,只需要一个合计.增加rollup之后,需要增加判断。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
b,
sum(c),sum(d),sum(e) from #t
group by a,b with rollup
having grouping(b)=0 or grouping(a)=1select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
b,
c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(c)=0 or grouping(a)=1
情况三:有多个分类汇总列,需要全部的小计和合计。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
另外一种显示小计的方式
select case when grouping(a)=1 then '合计'
when grouping(b)=1 then cast(a as varchar)+'小计'
else cast(a as varchar) end a,
case when grouping(b)=0 and grouping(c)=1
then cast(b as varchar)+'小计' else cast(b as varchar) end b,
case when grouping(c)=1 and grouping(b)=0
then '' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
情况四:有多个分类汇总列,需要部分的小计和合计
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(a)=1 or grouping(b)=0
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b,
c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(a)=1 or grouping(b)=1 or grouping(c)=