IDC资讯中心
/*–原帖地址:http://community.csdn.net/expert/topic/3845/3845290.xml?temp=.3689386–*/
–测试数据create table tb(编号 int,性质 varchar(10),数量 int,指标1 decimal(10,1),指标2 decimal)insert tb select 1 ,00 ,10,1.1 ,10union all select 2 ,01 ,20,1.2 ,20union all select 3 ,00 ,30,1.5 ,10union all select 4 ,01 ,40,1.9 ,35union 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.idfrom( 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<isnull(b.b,a.指标1-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<isnull(b.b,a.指标2-1) 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)bunion allselect 指标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=6from tbunion allselect 指标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=26from tbunion allselect 数量合计：,, isnull(sum(case 性质 when 00 then 数量 end),0), isnull(sum(case 性质 when 01 then 数量 end),0), id=30from tb)a order by idgo
–删除测试drop table tb
