无规律自定义分段的分类汇总+交叉表处理

--测试数据
create table tb(编号 int,性质 varchar(10),数量 int,指标1 decimal(10,1),指标2 decimal)
insert tb select 1 ,'00' ,10,1.1 ,10
union all select 2 ,'01' ,20,1.2 ,20
union all select 3 ,'00' ,30,1.5 ,10
union all select 4 ,'01' ,40,1.9 ,35
union all select 5 ,'00' ,40,1.2 ,20

/*--处理要求

要求得到下述结果:

a 范围 性质(00) 性质(01)
----------------- ---------------- -------------- --------------
指标1 <1.0 .00 .00
1.0-1.29 .63 .63
1.3-1.59 .38 .38
1.9-1.99 .00 .00
>=2 .00 .00
指标1平均值 1.27 1.55
指标2 <10 .00 .00
10-31 1.00 1.00
31-50 .00 .00
>=50 .00 .00
指标2平均值 13.33 27.50
数量合计: 80.00 60.00
------------------------------------------------------------------

分类说明:

范围 性质(00) 性质(01)
指标1 <1.0 0 0
1.0-1.29 (10+40)/(10+30+40) 20/(20+40)
1.3-1.59 30/(10+30+40) 0
1.6-1.99 0 40/(20+40)
>=2 0 0
指标1平均值: (1.1+1.5+1.2)/3 (1.2+1.9)/2

指标2 <10 0 0
10-30 (10+30+40)/(10+30+40) 20/(20+40)
31-50 0 40/(20+40)
>=50 0 0
指标2平均值: (10+10+20)/3 (20+35)/2

数量合计: 10+30+40 20+40
--*/
go

--查询处理
select a,范围,[性质(00)],[性质(01)]
from(
select
a=case a.id when 1 then '指标1' when 21 then '指标2' else '' end,
范围=a.lb,
[性质(00)]=cast(case when b.a>0 then isnull(a.a*1./b.a,0) else 0 end as decimal(10,2)),
[性质(01)]=cast(case when b.a>0 then isnull(a.a*1./b.a,0) else 0 end as decimal(10,2)),
a.id
from(
select b.id,b.lb,
a=sum(case a.性质 when '00' then a.数量 end),
b=sum(case a.性质 when '01' then a.数量 end)
from tb a
right join(
select id=1,lb='<1.0' ,a=null,b=1.0 union all
select id=2,lb='1.0-1.29',a=1.0 ,b=1.3 union all
select id=3,lb='1.3-1.59',a=1.3 ,b=1.9 union all
select id=4,lb='1.9-1.99',a=1.9 ,b=2.0 union all
select id=5,lb='>=2' ,a=2.0 ,b=null
)b on a.指标1>=isnull(b.a,a.指标1)
and a.指标1 group by b.id,b.lb
union all
select b.id,b.lb,
a=sum(case a.性质 when '00' then a.数量 end),
b=sum(case a.性质 when '01' then a.数量 end)
from tb a right join(
select id=21,lb='<10' ,a=null,b=10 union all
select id=22,lb='10-31',a=10 ,b=31 union all
select id=23,lb='31-50',a=31 ,b=51 union all
select id=25,lb='>=50' ,a=50 ,b=null
)b on a.指标2>=isnull(b.a,a.指标2)
and a.指标2 group by b.id,b.lb
)a,(
select
a=isnull(sum(case 性质 when '00' then 数量 end),0),
b=isnull(sum(case 性质 when '01' then 数量 end),0)
from tb
)b
union all
select '指标1平均值','',
cast(isnull(
case
when count(case 性质 when '00' then 性质 end)>0
then sum(case 性质 when '00' then 指标1 end)
*1./count(case 性质 when '00' then 性质 end)
else 0
end,0) as decimal(10,2)),
cast(isnull(
case
when count(case 性质 when '01' then 性质 end)>0
then sum(case 性质 when '01' then 指标1 end)
*1./count(case 性质 when '01' then 性质 end)
else 0
end,0) as decimal(10,2)),
id=6
from tb
union all
select '指标2平均值','',
cast(isnull(
case
when count(case 性质 when '00' then 性质 end)>0
then sum(case 性质 when '00' then 指标2 end)
*1./count(case 性质 when '00' then 性质 end)
else 0
end,0) as decimal(10,2)),
cast(isnull(
case
when count(case 性质 when '01' then 性质 end)>0
then sum(case 性质 when '01' then 指标2 end)
*1./count(case 性质 when '01' then 性质 end)
else 0
end,0) as decimal(10,2)),
id=26
from tb
union all
select '数量合计:','',
isnull(sum(case 性质 when '00' then 数量 end),0),
isnull(sum(case 性质 when '01' then 数量 end),0),
id=30
from tb
)a order by id
go

--删除测试
drop table tb

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8591181/viewspace-899288/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/8591181/viewspace-899288/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值