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

原创 2005年03月16日 09:02:00

/*--原帖地址:
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.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.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<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
)b
union all
select '指标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=6
from tb
union all
select '指标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=26
from tb
union all
select '数量合计:','',
 isnull(sum(case 性质 when '00' then 数量 end),0),
 isnull(sum(case 性质 when '01' then 数量 end),0),
 id=30
from tb
)a order by id
go

--删除测试
drop table tb

逐月对比的交叉表处理

  • 2012年11月22日 14:28
  • 50KB
  • 下载

仿IOS分段选择器(自定义RadioGroup)

  • 2015年11月17日 16:57
  • 5.33MB
  • 下载

模块管理常规功能自定义系统的设计与实现(41--终级阶段 综合查询[8]分类汇总)

综合查询(8)--分类汇总         这一节来看看分类汇总,在一个查询方案建立好了以后,可以对其进行汇总和分类汇总。分类汇总的级数可以任意级,为了方便操作,暂定为最多三级。     ...
  • jfok
  • jfok
  • 2014年05月13日 14:25
  • 2376

百度知道自定义分类采集版

  • 2017年08月18日 10:36
  • 637KB
  • 下载

自定义分类Tab菜单条

  • 2016年07月18日 17:54
  • 5.11MB
  • 下载

Android自定义ScrollView分段加载大文本数据到TextView

原文地址:http://www.cnblogs.com/tiantianbyconan/p/3311658.html 这是我现在碰到的一个问题,如果需要在TextView中加载大文本...

Android自定义ScrollView分段加载大文本数据到TextView

这是我现在碰到的一个问题,如果需要在TextView中加载大文本的时候,比如几M的txt文件时,TextView载入的时候会出现卡死的现象,甚至会出现异常等待退出出现。 解决办法之一就是通过“分...

Ecshop商品分类页实现自定义Title

  • 2014年02月12日 15:35
  • 31KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:无规律自定义分段的分类汇总+交叉表处理
举报原因:
原因补充:

(最多只允许输入30个字)