34,588
社区成员
发帖
与我相关
我的任务
分享
--比较Union all与with RollUP
/*(1)union all 对每级的汇总都必须单独的处理,最后才生成结果;而RollUP的分级汇总是MSSQL内部直接处理的,所以效率比Union all高
(2)RollUP固定对Group by子句中的字段进行汇总,而Union all可以针对某个级别进行汇总
(3)从上面的两个结果可以看出,使用Union all的文字说明比RolUP更为方便
*/
--(3)分级汇总过滤
/*由于RollUP产生的合计,小计汇总都可以通过Grouping函数来判读
*/
declare @T table
(
groups char(10),
Item varchar(10),
Color varchar(10),
Quantity int
)
insert into @T select 'aa','table','blue',124
union all select 'bb','table','red',-23
union all select 'bb','cup','green',-23
union all select 'aa','chair','blue',101
union all select 'aa','chair','red',-90
select groups,Item,color,sum(Quantity) Quantity,
GP_groups=grouping(groups),
GP_Item=grouping(Item),
GP_Color=grouping(Color)
from @T
group by groups,Item,color with rollup
having grouping(Item)=1 and grouping(groups)=0
/*
groups Item color Quantity GP_groups GP_Item GP_Color
---------- ---------- ---------- ----------- --------- ------- --------
aa chair blue 101 0 0 0
aa chair red -90 0 0 0
aa chair NULL 11 0 0 1
aa table blue 124 0 0 0
aa table NULL 124 0 0 1
aa NULL NULL 135 0 1 1
bb cup green -23 0 0 0
bb cup NULL -23 0 0 1
bb table red -23 0 0 0
bb table NULL -23 0 0 1
bb NULL NULL -46 0 1 1
NULL NULL NULL 89 1 1 1
*/
/*
观看GP_groups,GP_Item,GP_Color以上数据可以看出当结果为1的时候表示小计数据
为0的时候表示聚合行数据
*/
--下面加上having grouping(Item)=1 and grouping(groups)=0在看
select groups,Item,color,sum(Quantity) Quantity,
GP_groups=grouping(groups),
GP_Item=grouping(Item),
GP_Color=grouping(Color)
from @T
group by groups,Item,color with rollup
having grouping(Item)=1 and grouping(groups)=0
/*
groups Item color Quantity GP_groups GP_Item GP_Color
---------- ---------- ---------- ----------- --------- ------- --------
aa NULL NULL 135 0 1 1
bb NULL NULL -46 0 1 1
*/
/*
现在我们要用with rollup实现分级汇总结果显示格式
可以先看下3实例分级汇总过滤,在查询的时候实现grouping(列名)
*/
declare @T table
(
groups char(10),
Item varchar(10),
Color varchar(10),
Quantity int
)
insert into @T select 'aa','table','blue',124
union all select 'bb','table','red',-23
union all select 'bb','cup','green',-23
union all select 'aa','chair','blue',101
union all select 'aa','chair','red',-90
--select groups,Item,color,sum(Quantity) Quantity,
-- grouping(groups) gp,
-- grouping(Item) Item,
-- grouping(Color) Color
--from @T group by groups,Item,Color with rollup
select case when grouping(groups)=1 then '总计' else groups end as 'groups',
isnull(case when grouping(groups)=0 and grouping(Item)=1 then N'小计'+groups else Item end,'') as 'Item',
isnull(case when grouping(groups)=0 and grouping(Color)=1 and grouping(Item)=0 then Item+N'小计' else Color end,'') as 'Color',
sum(Quantity) Quantity
from @T group by groups,Item,Color with rollup
/*
groups Item Color Quantity
---------- ------------ ------------ -----------
aa chair blue 101
aa chair red -90
aa chair chair小计 11
aa table blue 124
aa table table小计 124
aa 小计aa 135
bb cup green -23
bb cup cup小计 -23
bb table red -23
bb table table小计 -23
bb 小计bb -46
总计 89
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/09/20/4572310.aspx
--(2)使用union all
declare @TB table
(
Item varchar(20),
Color varchar(20),
Quantity int
)
insert into @TB select 'Table','Blue',124
union all select 'Table','Red',-23
union all select 'Chair','Blue',101
union all select 'Chair','Red',91
select Item,Color,Quantity from @TB where Item='Table'
union all
select '',Item as 'TableTotal',sum(Quantity) as TableQua from @TB where Item='Table'group by Item
union all
select Item,Color,Quantity from @TB where Item='Chair'
union all
select '',Item as 'ChairTotal',sum(Quantity) as TableQua from @TB where Item='Chair'group by Item
union all
select 'Total','',sum(Quantity) as Quantity from @TB
--Results
/*
Item Color Quantity
-------------------- -------------------- -----------
Table Blue 124
Table Red -23
Table 101
Chair Blue 101
Chair Red 91
Chair 192
Total 293
(7 行受影响)
*/
--使用with RollUp处理上述问题
declare @TB table
(
Item varchar(20),
Color varchar(20),
Quantity int
)
insert into @TB select 'Table','Blue',124
union all select 'Table','Red',-23
union all select 'Chair','Blue',101
union all select 'Chair','Red',91
select Item,Color,sum(Quantity) Quantity
from @TB
group by Item,Color with rollup
--Results
/*
Item Color Quantity
-------------------- -------------------- -----------
Chair Blue 101
Chair Red 91
Chair NULL 192
Table Blue 124
Table Red -23
Table NULL 101
NULL NULL 293
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/09/20/4572310.aspx
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)
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, sum(c) as c,sum(d) as d,sum(e) as e
from
#t
group by
a,b
with rollup
having grouping(b)=0 or grouping(a)=1
/*a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
2 2 14 16 26
2 3 15 16 26
合计 NULL 53 64 104
(所影响的行数为 5 行)
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, c, sum(d) as d,sum(e) as e
from
#t
group by
a,b,c
with rollup
having grouping(c)=0 or grouping(a)=1
a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 3 16 26
1 3 3 16 26
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 3 3 12 21
2 3 6 4 5
合计 NULL NULL 64 104
(所影响的行数为 9 行)
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) as d,sum(e) as e
from
#t
group by
a,b,c
with rollup
having grouping(a)=1 or grouping(b)=0
a b c d e
------------------------------ ----------- ------------------------------ ----------- -----------
1 2 3 16 26
1 2 小计 16 26
1 3 3 16 26
1 3 小计 16 26
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 2 小计 16 26
2 3 3 12 21
2 3 6 4 5
2 3 小计 16 26
合计 NULL NULL 64 104
(所影响的行数为 13 行)
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, sum(c) as c, sum(d) as d,sum(e) as e from #t
group by
a,b,c
with rollup
having grouping(a)=1 or grouping(b)=1 or grouping(c)=0
drop table #t
a b c d e
------------------------------ ------------------------------ ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
1 小计 24 32 52
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 3 9 12 21
2 3 6 4 5
2 小计 29 32 52
合计 NULL 53 64 104
(所影响的行数为 11 行)
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/09/24/4587789.aspx
select * from(
select pname ,sex ,city ,buycount from mytab
union all
select city+'小计','','',sum(buycount) from mytab group by city
union all
select '总合计','','',sum(buycount) from mytab
) t
order by pname
create table MyTab(pname varchar(10),sex varchar(10),city varchar(20),buycount int)
insert into MyTab values('张三' ,'男', '北京', 10)
insert into MyTab values('李四' ,'男', '北京', 20)
insert into MyTab values('王五' ,'女', '上海', 20)
go
select * from
(
select * from mytab
union all
select pname = '' , sex = '' , city + '小计' city , sum(buycount) from MyTab group by city + '小计'
union all
select pname = '' , sex = '' , '总合计' city , sum(buycount) from MyTab
) t
order by case when charindex('总合计' , city) > 0 then 2 else 1 end , city
drop table mytab
/*
pname sex city buycount
---------- ---------- ------------------------ -----------
张三 男 北京 10
李四 男 北京 20
北京小计 30
王五 女 上海 20
上海小计 20
总合计 50
(所影响的行数为 6 行)
*/
select pname,sex,city,buycount=sum(buycount)
from MyTab
Group by pname,sex,city,buycount with ROLLUP
having grouping(pname)=1 and grouping(sex)=1 and grouping(city)=1
select * from
(
select * from mytab
union all
select pname = '' , sex = '' , city + '小计' city , sum(buycount) from MyTab group by city + '小计'
union all
select pname = '' , sex = '' , '总合计' city , sum(buycount) from MyTab
) t
order by case when charindex('总合计' , city) then 2 else 1 end , city