/--原帖地址:
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 ,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.a1./b.a,0) else 0 end as decimal(10,2)),
[性质（01）]=cast(case when b.a>0 then isnull(a.a1./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
1<isnull(b.b,a.指标1-1) '00'="" '01'="" ,a="null,b=10" ,b="51" a="" a.性质="" a.数量="" all="" b="sum(case" b.id,b.lb="" b.id,b.lb,="" by="" end)="" end),="" from="" group="" id="25,lb='" join(="" right="" select="" tb="" then="" union="" when="">=50' ,a=50 ,b=null
2)b on a.指标2>=isnull(b.a,a.指标2)
3and a.指标2<isnull(b.b,a.指标2-1) '00'="" '01'="" '指标1平均值','',="" )a,(="" )b="" a="isnull(sum(case" all="" b="isnull(sum(case" b.id,b.lb="" by="" case="" cast(isnull(="" count(case="" end)="" end),0)="" end),0),="" from="" group="" select="" tb="" then="" union="" when="" 性质="" 数量="">0
4then sum(case 性质 when '00' then 指标1 end)
5*1./count(case 性质 when '00' then 性质 end)
6else 0
7end,0) as decimal(10,2)),
8cast(isnull(
9case
10when count(case 性质 when '01' then 性质 end)>0
11then sum(case 性质 when '01' then 指标1 end)
12*1./count(case 性质 when '01' then 性质 end)
13else 0
14end,0) as decimal(10,2)),
15id=6
16from tb
17union all
18select '指标2平均值','',
19cast(isnull(
20case
21when count(case 性质 when '00' then 性质 end)>0
22then sum(case 性质 when '00' then 指标2 end)
23*1./count(case 性质 when '00' then 性质 end)
24else 0
25end,0) as decimal(10,2)),
26cast(isnull(
27case
28when count(case 性质 when '01' then 性质 end)>0
29then sum(case 性质 when '01' then 指标2 end)
30*1./count(case 性质 when '01' then 性质 end)
31else 0
32end,0) as decimal(10,2)),
33id=26
34from tb
35union all
36select '数量合计：','',
37isnull(sum(case 性质 when '00' then 数量 end),0),
38isnull(sum(case 性质 when '01' then 数量 end),0),
39id=30
40from tb
41)a order by id
42go
43
44\--删除测试
45drop table tb</isnull(b.b,a.指标2-1)></isnull(b.b,a.指标1-1)>