转载注明出错,谢谢!!
view plaincopy to clipboardprint?
--(1)数据统计与汇总
declare @Tb table
(
ID int identity(1,1) primary key,
EmpoyeeName varchar(50),
Salary int
)
insert into @Tb select 'aa',1200
union all select 'bb',1300
union all select 'cc',2400
union all select 'bb',900
union all select 'bb',1800
union all select 'cc',700
union all select 'aa',600
select EmpoyeeName,
sum(Salary) Salary
from @Tb where Salary>1000 group by EmpoyeeName
--派生数据
--部门表
declare @Dept table
(
ID int identity(1,1) primary key,
DepartMent nvarchar(20)
)
insert into @Dept select 'A部门'
insert into @Dept select 'B部门'
insert into @Dept select 'C部门'
--员工表
declare @Employee table
(
ID int identity(1,1) primary key,
[Name] varchar(20),
DeptID int--与部门表中的ID相关联
)
insert into @Employee select '胡果',1
insert into @Employee select '小梁',1
insert into @Employee select '近身剪',2
insert into @Employee select '树哥',3
--订单表
declare @Order table
(
ID int identity(1,1) primary key,
EmployeeID int,--与员工表中的ID相关联
SalePrice decimal(10,2),
Date datetime
)
insert into @Order select 1,1000.00,'2009-1-1'
union all select 1,900.00,'2009-3-1'
union all select 2,800.00,'2009-3-8'
union all select 2,700.00,'2009-3-18'
union all select 3,1200.00,'2009-3-10'
union all select 3,1200.00,'2009-4-10'
union all select 3,600.00,'2009-5-1'
union all select 4,900.00,'2009-1-18'
union all select 4,900.00,'2009-2-18'
union all select 4,900.00,'2009-4-18'
union all select 4,600.00,'2009-5-11'
;with hgo as
(
select D.DepartMent,Convert(varchar(6),dateadd(month,D.[month],'20081201'),112) 'Month',
isnull(sum(O.SalePrice),0) SalePrice
from
(
select D.ID,D.DepartMent,M.[month]
from @Dept D
cross join
(
select [month]=1 union all
select [month]=2 union all
select [month]=3 union all
select [month]=4 union all
select [month]=5 union all
select [month]=6
)M
) D
left join
(
select E.DeptID,O.SalePrice,O.Date from @Employee E
join @Order O on E.ID=O.EmployeeID
) O on O.DeptID=D.ID and
O.Date>=dateadd(month,D.[month],'20081201')
and O.Date<dateadd(month,D.[month],'20090101')
group by D.DepartMent,D.[month]
)
select DepartMent,
sum(case when Month='200901' then SalePrice else 0 end) '一月份销售',
sum(case when Month='200902' then SalePrice else 0 end) '二月份销售',
sum(case when Month='200903' then SalePrice else 0 end) '三月份销售',
sum(case when Month='200904' then SalePrice else 0 end) '四月份销售',
sum(case when Month='200905' then SalePrice else 0 end) '五月份销售',
sum(case when Month='200906' then SalePrice else 0 end) '六月份销售',
sum(SalePrice) '上半年总业绩'
from hgo group by DepartMent
/*
DepartMent 一月份销售 二月份销售 三月份销售 四月份销售 五月份销售 六月份销售 上半年总业绩
--------- ---------- -------- ---------- ----------- ---------- ---------- ------------
A部门 1000.00 0.00 2400.00 0.00 0.00 0.00 3400.00
B部门 0.00 0.00 1200.00 1200.00 600.00 0.00 3000.00
C部门 900.00 900.00 0.00 900.00 600.00 0.00 3300.00
*/
--(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
*/
--比较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