• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

统计问题

blackhawk_yps 2003-05-09 04:05:35
下面是一个典型的统计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或更多的情况呢),希望得到大家的指点。
...全文
11 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
blackhawk_yps 2003-05-13
来结贴的,不过看到了pengdali(大力) 的答复,对condition1+condition2+condition3+......+condition10=10 这句话不太理解,condition这个值是怎么来的?我这里的condition1=true指是一个抽象的条件,具体的可能是substring(field1,1,2)='aa'这样一个条件,这时候你的等式里的condition值怎么得到?请教
回复
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
不好意思,打错了!
^_^
回复
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

通过对标志位的判断就可以设size11为0或1了,
后面只需对@temp进行累加继续对标志位进行判断就行了
^_^
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-05-09 04:05
社区公告
暂无公告