统计问题

[blackhawk_yps]

下面是一个典型的统计sql语句
select
sum(case when condition1=true then 1 else 0 end) as size1,
sum(case when condition2=true then 1 else 0 end) as size2,
sum(case when condition3=true then 1 else 0 end) as size3,
sum(case when condition4=true then 1 else 0 end) as size4,
sum(case when condition5=true then 1 else 0 end) as size5,
sum(case when condition6=true then 1 else 0 end) as size6,
sum(case when condition7=true then 1 else 0 end) as size7,
sum(case when condition8=true then 1 else 0 end) as size8,
sum(case when condition9=true then 1 else 0 end) as size9,
sum(case when condition10=true then 1 else 0 end) as size10
from tables
where ...
如果这时候我想增加一个统计项目size11,他的条件是以上condition1-condition10都不成立。即sum(case (conditon1=false and condition2=false ... and condition10=false) then 1 else 0 end) as size11。
但是这样写不论是这个sql语句的大小、效率都不会让人满意（我有condition50或更多的情况呢），希望得到大家的指点。

[psxfghost]

declare @flag int
set @flag=0
declare @temp int
select @temp=sum(*) from (select
sum(case when condition1=true then 1 else 0 end) as size1,
sum(case when condition2=true then 1 else 0 end) as size2,
sum(case when condition3=true then 1 else 0 end) as size3,
sum(case when condition4=true then 1 else 0 end) as size4,
sum(case when condition5=true then 1 else 0 end) as size5,
sum(case when condition6=true then 1 else 0 end) as size6,
sum(case when condition7=true then 1 else 0 end) as size7,
sum(case when condition8=true then 1 else 0 end) as size8,
sum(case when condition9=true then 1 else 0 end) as size9,
sum(case when condition10=true then 1 else 0 end) as size10
from tables
where ...
) as a
if (@temp>0)
set @flag=1

通过对标志位的判断就可以设size11为0或1了,
后面只需对@temp进行累加继续对标志位进行判断就行了
^_^

[psxfghost]

不好意思,打错了!
^_^

[Happiness]

select size1,size2,size3,size4,size5,size6,size7,size8,size9,size10,allsize-(
size1+size2+size3+size4+size5+size6+size7+size8+size9+size10) as size11
from (
select
sum(case when condition1=true then 1 else 0 end) as size1,
sum(case when condition2=true then 1 else 0 end) as size2,
sum(case when condition3=true then 1 else 0 end) as size3,
sum(case when condition4=true then 1 else 0 end) as size4,
sum(case when condition5=true then 1 else 0 end) as size5,
sum(case when condition6=true then 1 else 0 end) as size6,
sum(case when condition7=true then 1 else 0 end) as size7,
sum(case when condition8=true then 1 else 0 end) as size8,
sum(case when condition9=true then 1 else 0 end) as size9,
sum(case when condition10=true then 1 else 0 end) as size10,
sum(1) as AllSize
from tables
where ...
) as tmp

[psxfghost]

对应的语句更改如下:
select @temp=a.sumall from (select
sum(case when condition1=true then 1 else 0 end)+
sum(case when condition2=true then 1 else 0 end)+
sum(case when condition3=true then 1 else 0 end)+
sum(case when condition4=true then 1 else 0 end)+
sum(case when condition5=true then 1 else 0 end)+
sum(case when condition6=true then 1 else 0 end)+
sum(case when condition7=true then 1 else 0 end)+
sum(case when condition8=true then 1 else 0 end)+
sum(case when condition9=true then 1 else 0 end)+
sum(case when condition10=true then 1 else 0 end) as sumall
from tables a
where ...

[pengdali]

select *,case when condition1+condition2+condition3+......+condition10=10 then 1 else 0 end size11 from (
select sum(case when condition1=true then 1 else 0 end) as size1,
sum(case when condition2=true then 1 else 0 end) as size2,
sum(case when condition3=true then 1 else 0 end) as size3,
sum(case when condition4=true then 1 else 0 end) as size4,
sum(case when condition5=true then 1 else 0 end) as size5,
sum(case when condition6=true then 1 else 0 end) as size6,
sum(case when condition7=true then 1 else 0 end) as size7,
sum(case when condition8=true then 1 else 0 end) as size8,
sum(case when condition9=true then 1 else 0 end) as size9,
sum(case when condition10=true then 1 else 0 end) as size10
from tables
where ...
) tem

[blackhawk_yps]

来结贴的，不过看到了pengdali(大力) 的答复，对condition1+condition2+condition3+......+condition10=10 这句话不太理解，condition这个值是怎么来的？我这里的condition1=true指是一个抽象的条件，具体的可能是substring(field1,1,2)='aa'这样一个条件，这时候你的等式里的condition值怎么得到？请教