sql server 关于 数据汇总分析

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