简化语句

简化语句 [已结贴,结贴人:jjwwang]

jjwwang
风归叶
等级:

发表于：2008-04-08 11:09:43 楼主

...
sum(case when rq <'1997-10-1' and left(BM,1)='0' then 1 else 0 end) _0_sum,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='000502' then 1 else 0 end) _0_1,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='000201' then 1 else 0 end) _0_2,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='000404' then 1 else 0 end) _0_3,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='000403' then 1 else 0 end) _0_4,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and left(BM,4)='0008' then 1 else 0 end) _0_5,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='0010' and BM <>'001001'then 1 else 0 end),
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='001001' then 1 else 0 end) _0_7,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='000503' then 1 else 0 end) _0_8,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='000504' then 1 else 0 end) _0_9,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='000507' then 1 else 0 end) _0_10,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='000601' then 1 else 0 end) _0_11,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and left(BM,4)='0012' then 1 else 0 end) _0_12,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM in('000510','000509') then 1 else 0 end) _0_13,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='000505' then 1 else 0 end) _0_14,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM='000506' then 1 else 0 end) _0_15,
sum(case when rq <'1997-10-1' and left(BM,1)='0' and BM not in('000502','000201','000404','000403','001001','000503',
'000504','000507','000601','000510','000509','000505','000506')
and left(BM,4) not in('0008','0010','0012') then 1 else 0 end) _0_16,

一个储存过程一段代码。
即在列转行的时候，上面case 很多条件，最后求不包含上面case条件的做统计，很累。
有什么好办法优化一下，即不用再写

问题点数：100 回复次数：17 修改删除举报引用回复

select *, _0_16=_0_sum - _0_1 - _0_2 - _0_3 - ... - _0_15 from ( select ... count(1) _0_sum, sum(case when BM='000502' then 1 else 0 end) _0_1, sum(case when BM='000201' then 1 else 0 end) _0_2, ... where rq<'1997-10-1' and left(BM,1)='0' ... ) T

--所有的case中都包含rq <'1997-10-1' and left(BM,1)='0'，可以把rq <'1997-10-1' and left(BM,1)='0'放在最后的where条件中。这样可以缩小范围，并且减少判断。 count(1) _0_sum, sum(case when BM='000502' then 1 else 0 end) _0_1, sum(case when BM='000201' then 1 else 0 end) _0_2, sum(case when BM='000404' then 1 else 0 end) _0_3, sum(case when BM='000403' then 1 else 0 end) _0_4, sum(case when left(BM,4)='0008' then 1 else 0 end) _0_5, sum(case when BM='0010' and BM <>'001001'then 1 else 0 end), sum(case when BM='001001' then 1 else 0 end) _0_7, sum(case when BM='000503' then 1 else 0 end) _0_8, sum(case when BM='000504' then 1 else 0 end) _0_9, sum(case when BM='000507' then 1 else 0 end) _0_10, sum(case when BM='000601' then 1 else 0 end) _0_11, sum(case when left(BM,4)='0012' then 1 else 0 end) _0_12, sum(case when BM in('000510','000509') then 1 else 0 end) _0_13, sum(case when BM='000505' then 1 else 0 end) _0_14, sum(case when BM='000506' then 1 else 0 end) _0_15, sum(BM not in('000502','000201','000404','000403','001001','000503', '000504','000507','000601','000510','000509','000505','000506') and left(BM,4) not in('0008','0010','0012') then 1 else 0 end) _0_16, where rq <'1997-10-1' and left(BM,1)='0'

sum(BM in('000502','000201','000404','000403','001001','000503', '000504','000507','000601','000510','000509','000505','000506') or left(BM,4) in('0008','0010','0012') then 0 else 1 end) _0_16,