简化语句
...
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条件的做统计，很累。
有什么好办法优化一下，即不用再写

 


------解决方案--------------------
rq <'1997-10-1' and left(BM,1)='0'
这个可以放到where后面
------解决方案--------------------

写成动态的
------解决方案--------------------
所有的sum,case when条件中都有 rq<.... and left..这一句.
那么,将这一句提出来,放到 where 过滤中去. 
那么将是

sum(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 过滤),那么看你实际的需要,是否要把不符条件的也列出来,只不过sum值为0. 如果要列出来,那么多一次连表就可以了.但效率会降一点.偷懒的后果.

不含case when不可能,由于你条件不致,也就是没有规则,无法用动态行转列.即便是可以用动态行转列来写,其实还是case when完成的.

要怕麻烦,就放到前台程序去做.

只想告诉你一点,在数据库端做行转列都是没意义的事, 有人说,是老板要求这么做的,我只想问,老板会趴在你的查询分析器上看结果?
------解决方案--------------------
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, 

这个可以去掉，或者改一下：

SQL code

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

------解决方案--------------------
left(BM,1)='0' and BM='000502'  
这个两个重复了，后面一个成立话，前面一个肯定也成立，所以前面一个直接去掉

------解决方案--------------------
SQL code
 
 --所有的case中都包含rq  <'1997-10-1' and left(BM,1)='0'，可以把rq  <'1997-10-1' and left(BM,1)='0'放在最后的where条件中。这样可以缩小范围，并且减少判断