sql server 关于 数据汇总分析

hongblue

　　CUBE 和 ROLLUP 之间的区别在于：
　　CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
　　ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
　　
例子：
　　
if object_id('a') is not null
drop table a
create table a
(
bm varchar(20), --编码
ck varchar(2), --仓库
sl int --数量
)
　　insert into a
select '01' ,'a', 6
union all
select '01','b',7
union all
select '02','a',8
union all
select '02','b',9
　　select * from a
　　
（1）rollup 的用法
SELECT bm, hj=
case when grouping(ck)=0
then
ck
else
bm+'合计'
end
, Sum(sl) as sl
FROM A
GROUP BY bm, ck WITH ROLLUP
　　
/*
　　
01 a 6
01 b 7
01 01合计 13
02 a 8
02 b 9
02 02合计 17
NULL NULL 30
　　
　　*/
　　
　　
　　（2）关于cuble的基本用法。
　　SELECT bm, ck, Sum(sl) as sl
FROM A
GROUP BY bm,ck WITH cube
　　

　　
　　
　　
　　
　　
　　/*
　　
　　01 a 6
01 b 7
01 NULL 13
02 a 8
02 b 9
02 NULL 17
NULL NULL 30
NULL a 14
NULL b 16
　　
　　*/
　　
　　
　　
　　（1） 不使用用rollup实现汇总的结果。
　　if object_id('ss') is not null
　　drop table ss
　　
create table ss
(
gys varchar(10),
wlbm varchar(5),
jcsl int,
hgsl int
)
delete ss
insert into ss values('华南集团','001',500,500)
insert into ss values('华南集团','001',200,0)
insert into ss values('华南集团','001',100,100)
insert into ss values('华南集团','002',200,0)
insert into ss values('华南集团','002',100,100)
insert into ss values('江林集团','001',600,600)
insert into ss values('江林集团','001',200,0)
insert into ss values('花海斯通','002',200,200)
insert into ss values('花海斯通','002',100,0)
　　
select 供应商,物料编号,检测批次总数,合格数量,pj as '平均' from
(
select sum(zs) as '检测批次总数',gys as '供应商','总计' as'物料编号' ,sum(hgsl) as '合格数量' ,
　　(cast(sum (hgsl) as float)/cast(sum(zs) as float)) as 'pj'
　　from
(select count(*) as 'zs' ,gys ,wlbm ,sum (case when hgsl=0 then 0 else 1 end) as 'hgsl' ,
　　(cast(sum (case when hgsl=0 then 0 else 1 end) as float)/cast(count(*) as float)) as 'pj'
　　from ss group by gys , wlbm) t group by gys
　　union all
　　select count(*) as 'zs' ,gys ,wlbm ,sum (case when hgsl=0 then 0 else 1 end) as 'hgsl' ,
　　(cast(sum (case when hgsl=0 then 0 else 1 end) as float)/cast(count(*) as float)) as 'pj'
　　from ss group by gys,wlbm) j order by CHARINDEX(left(供应商,2),'华南,江林,花海'),物料编号 asc
　　/*
　　华南集团 001 3 2 0.66666666666666663
　　华南集团 002 2 1 0.5
　　华南集团 总计 5 3 0.59999999999999998
　　江林集团 001 2 1 0.5
　　江林集团 总计 2 1 0.5
　　花海斯通 002 2 1 0.5
　　花海斯通 总计 2 1 0.5
　　*/
　　(2)通过 rollup实现数据汇总：
　　if object_id('[tb]') is not null
drop table [tb]
go
create table [tb]([供应商] varchar(8),[物料编码] varchar(10),[检验数量] int,[合格数量] int)
insert [tb]
select '华南集团','001',500,500 union all
select '华南集团','001',200,0 union all
select '华南集团','001',100,100 union all
select '华南集团','002',200,0 union all
select '华南集团','002',100,100 union all
select '江林集团','001',600,600 union all
select '江林集团','001',200,0 union all
select '花海斯通','002',200,200 union all
select '花海斯通','002',100,0
go
select
供应商=case when GROUPING(物料编码)=0 then 供应商 else 供应商+'合计' end,
物料编码=case when GROUPING(物料编码)=0 then 物料编码 else '' end,
检验批次总数=count(*),
合格批次总数=sum(case when 合格数量<>0 then 1 else 0 end),
比例=cast(sum(case when 合格数量<>0 then 1 else 0 end)*1.0/count(*)as dec(18,2))
from tb
group by 供应商,物料编码 with rollup
having GROUPING(供应商)=0
order by CHARINDEX(left(供应商,2),'华南,江林,花海')
供应商 物料编码 检验批次总数 合格批次总数 比例
------------ ---------- ----------- ----------- ---------------------------------------
华南集团 001 3 2 0.67
华南集团 002 2 1 0.50
华南集团合计 5 3 0.60
江林集团 001 2 1 0.50
江林集团合计 2 1 0.50
花海斯通 002 2 1 0.50
花海斯通合计 2 1 0.50