# 统计问题

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 ...

...全文
11 点赞 收藏 6

6 条回复

blackhawk_yps 2003-05-13

pengdali 2003-05-10
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

psxfghost 2003-05-09

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 ...

Happiness 2003-05-09
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 2003-05-09
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

^_^

3.3w+

MS-SQL Server相关内容讨论专区