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

/*--原帖地址: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

原文转自：http://www.ltesting.net