/*--原帖地址: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
延伸阅读
文章来源于领测软件测试网 https://www.ltesting.net/
