统计与汇总1

转载注明出错，谢谢！！

--(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
*/