统计与汇总1(转)

转载注明出错，谢谢！！

 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

转载于:https://www.cnblogs.com/ghd2004/articles/1700306.html