统计与汇总1

最新推荐文章于 2024-10-08 15:18:03 发布

最新推荐文章于 2024-10-08 15:18:03 发布 · 93 阅读

转载注明出错，谢谢！！

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