Irregular Custom Segment Classification summary + cross-table processing

/*--Original post address: http://community.csdn.net/expert/topic/3845/3845290.xml?temp=.3689386--*/

--Test data CREATE TABLE TB (number int, property varchar (10), quantity int, metric 1 decimal (10,1), metric 2 decimal) Insert TB Select 1, ', ', 10,1.1, 10union All select 2, ' is ', 20,1.2, 20union all select 3, ' More ', 30,1.5, 10union all Select 4, ' A ', 40,1.9, 35union all Select 5 , ' 00 ', 40,1.2, 20

/*--Processing Requirements

The following results were requested:

a                       Scope                 Property (00)          Property         -------- -----------------------------------------------------Index 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 Indicator 1 Average                          1.27           1.55 Indicator 2                <10               .00             00                           10-31            1.00           1.00                          31-50             .00             .00                          >=50              .00            00 indicator 2 Average                       Total number of     13.33          27.50:                            80.00          60.00---------------------- --------------------------------------------

Classification Description:

                     Scope             Quality (+)                                  Property (01) Index 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 Indicator 1 average:              (1.1+1.5+1.2 )/3                           (1.2+1.9)/2

Index 2        <10               0                                                        0                   10-30            (10+30+40)/(10+30+40)            20/(20+40)                     31-50             0                                                      40/(20+40)                     >=50             0                                                       0 index 2 average:                   (10+10+20)/3                         (20+35)/2

Total Quantity: 10+30+40 20+40--*/go

--Query processing Select a, scope, [Property (00)],[Property ($)]from (select  a=case a.id when 1 Then ' indicator 1 ' when then ' indicator 2 ' Else ' end,  range =a.lb, [Property]=cast (case when b.a>0 then IsNull (a.a*1./b.a,0) Else 0 end as decimal (10,2)),  [Property (a)]=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, & Nbsp;a=sum (case A. Nature when ' then ' A. Quantity end),   b=sum (case a. The nature when ' then a. Quantity end)  from TB a &NBSP;&N Bsp;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. Indicator 1>=isnull (b.a,a. Indicator 1)     and A. Indicator 1<isnull (b.b,a. Indicators 1-1)  group by b.id,b. lb union All select B.id,b.lb,  a=sum (case A. Nature when ' then a. Quantity end),   b=sum (case A. Nature when ' then a. Quantity End '  from TB a RIGHT join (  select id=21,lb= ' <10 '  ,a=null,b=10  Unio n 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. Indicator 2>=isnull (B.A, A. Indicator 2   and A. Indicator 2<isnull (b.b,a. Indicators 2-1)  group by b.id,b.lb) A, ( select   a=isnull ( SUM (case nature when ' then quantity end], 0),   b=isnull (SUM (case property when ' then Quantity end), 0)  from TB) bunion Allse Lect ' indicator 1 average ', ',  cast (IsNull (  case    when count (case nature when ' then nature end) >0& Nbsp;  then sum (case nature when ' then indicator 1 end]     *1./count (case nature when ' then property end) & Nbsp;  else 0   end,0) as decimal (10,2),  cast (IsNull (  case    when count (case property when ' then Nature end) >0    then sum (case nature when ' then indicator 1 end]     *1./count (case nature when ' a ' then property end)     else 0   end,0) as decimal (10,2),  id=6from tbunion allselect ' indicator 2 average ', ',  cast (IsNull (   case    when Count (case property when ' then Nature end ") >0   then sum (case nature when ' "Then indicator 2 End"     *1./count (case nature when ' then Nature end)    else 0   end , 0) as decimal (10,2),  cast (IsNull (  case    when count (case property when ' then Nature end) > 0   then sum (case nature when ' then indicator 2 end]     *1./count (case nature when ' a ' then property end )    else 0   end,0) as decimal (10,2),  id=26from tbunion allselect ' number total: ', ',  IsNull (sum (case nature when ' then quantity end), 0, IsNull (sum (case nature when ' then quantity end), 0),  id=30from TB) an ORDER by Idgo

--Delete test drop table TB