/*--原帖地址:http://community.csdn.net/expert/topic/3845/3845290.xml?temp=.3689386--*/
--測試數(shù)據(jù)create table tb(編號 int,性質(zhì) varchar(10),數(shù)量 int,指標(biāo)1 decimal(10,1),指標(biāo)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
/*--處理要求
要求得到下述結(jié)果:
a 范圍 性質(zhì)（00） 性質(zhì)（01） ----------------- ---------------- -------------- -------------- 指標(biāo)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指標(biāo)1平均值 1.27 1.55指標(biāo)2 <10 .00 .00 10-31 1.00 1.00 31-50 .00 .00 >=50 .00 .00指標(biāo)2平均值 13.33 27.50數(shù)量合計： 80.00 60.00------------------------------------------------------------------
分類說明:
范圍 性質(zhì)（00） 性質(zhì)（01）指標(biāo)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指標(biāo)1平均值： (1.1+1.5+1.2)/3 (1.2+1.9)/2
指標(biāo)2 <10 0 0 10-30 (10+30+40)/(10+30+40) 20/(20+40) 31-50 0 40/(20+40) >=50 0 0指標(biāo)2平均值： (10+10+20)/3 (20+35)/2
數(shù)量合計： 10+30+40 20+40--*/go
--查詢處理select a,范圍,[性質(zhì)（00）],[性質(zhì)（01）]from(select a=case a.id when 1 then '指標(biāo)1' when 21 then '指標(biāo)2' else '' end, 范圍=a.lb, [性質(zhì)（00）]=cast(case when b.a>0 then isnull(a.a*1./b.a,0) else 0 end as decimal(10,2)), [性質(zhì)（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.性質(zhì) when '00' then a.數(shù)量 end), b=sum(case a.性質(zhì) when '01' then a.數(shù)量 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.指標(biāo)1>=isnull(b.a,a.指標(biāo)1) and a.指標(biāo)1<isnull(b.b,a.指標(biāo)1-1) group by b.id,b.lb union all select b.id,b.lb, a=sum(case a.性質(zhì) when '00' then a.數(shù)量 end), b=sum(case a.性質(zhì) when '01' then a.數(shù)量 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.指標(biāo)2>=isnull(b.a,a.指標(biāo)2) and a.指標(biāo)2<isnull(b.b,a.指標(biāo)2-1) group by b.id,b.lb)a,( select a=isnull(sum(case 性質(zhì) when '00' then 數(shù)量 end),0), b=isnull(sum(case 性質(zhì) when '01' then 數(shù)量 end),0) from tb)bunion allselect '指標(biāo)1平均值','', cast(isnull( case when count(case 性質(zhì) when '00' then 性質(zhì) end)>0 then sum(case 性質(zhì) when '00' then 指標(biāo)1 end) *1./count(case 性質(zhì) when '00' then 性質(zhì) end) else 0 end,0) as decimal(10,2)), cast(isnull( case when count(case 性質(zhì) when '01' then 性質(zhì) end)>0 then sum(case 性質(zhì) when '01' then 指標(biāo)1 end) *1./count(case 性質(zhì) when '01' then 性質(zhì) end) else 0 end,0) as decimal(10,2)), id=6from tbunion allselect '指標(biāo)2平均值','', cast(isnull( case when count(case 性質(zhì) when '00' then 性質(zhì) end)>0 then sum(case 性質(zhì) when '00' then 指標(biāo)2 end) *1./count(case 性質(zhì) when '00' then 性質(zhì) end) else 0 end,0) as decimal(10,2)), cast(isnull( case when count(case 性質(zhì) when '01' then 性質(zhì) end)>0 then sum(case 性質(zhì) when '01' then 指標(biāo)2 end) *1./count(case 性質(zhì) when '01' then 性質(zhì) end) else 0 end,0) as decimal(10,2)), id=26from tbunion allselect '數(shù)量合計：','', isnull(sum(case 性質(zhì) when '00' then 數(shù)量 end),0), isnull(sum(case 性質(zhì) when '01' then 數(shù)量 end),0), id=30from tb)a order by idgo
--刪除測試drop table tb
