现在遇到了一个问题.为了提高SQL查询效率.现在想把存储过程中,几条相似的SQL语句合并成一条。举个例子吧:
select @tcount0=count(*) from atable where jwv1>100 and jwv1<200 select @tcount1=count(*) from atable where jwv1>200 and jwv1<300 select @tcount2=count(*) from atable where jwv1>300 and jwv1<400 select @tcount3=count(*) from atable where jwv1>400 and jwv1<500 select @tcount4=count(*) from atable where jwv1>500 and jwv1<600 想把这几条类似的统计语句合并成一行,我知道用CASE 也可以。可是在THEN 后面只跟着常量.要合并成一条语句,该怎么做呢?
请教高手.谢谢
select @tcount0=count(*) from atable where jwv1>100 and jwv1<200 select @tcount1=count(*) from atable where jwv1>200 and jwv1<300 select @tcount2=count(*) from atable where jwv1>300 and jwv1<400 select @tcount3=count(*) from atable where jwv1>400 and jwv1<500 select @tcount4=count(*) from atable where jwv1>500 and jwv1<600 想把这几条类似的统计语句合并成一行,我知道用CASE 也可以。可是在THEN 后面只跟着常量.要合并成一条语句,该怎么做呢?
请教高手.谢谢
select @tcount0=count(*) from atable where jwv1>100 and jwv1 <200
union all
select @tcount1=count(*) from atable where jwv1>200 and jwv1 <300
union all
select @tcount2=count(*) from atable where jwv1>300 and jwv1 <400
union all
select @tcount3=count(*) from atable where jwv1>400 and jwv1 <500
union all
select @tcount4=count(*) from atable where jwv1>500 and jwv1 <600
@tcount1=sum(case when jwv1>100 and jwv1 <200 then 1 else 0 end),
@tcount2=sum(case when jwv1>200 and jwv1 <300 then 1 else 0 end),
......
from atable
when jwv1>200 and jwv1 <300 then 1
............
else 0 end)
from atable
group by ....
@tcount1 = COUNT(CASE WHEN jwv1 > 100 AND jwv1 < 200 THEN 1 END),
@tcount2 = COUNT(CASE WHEN jwv1 > 200 AND jwv1 < 300 THEN 1 END),
...
...
FROM atable
WHERE jwv1 > 100 AND jwv1 < 600;
@tcount1=sum(case when jwv1>100 and jwv1 <200 then 1 else 0 end),
@tcount2=sum(case when jwv1>200 and jwv1 <300 then 1 else 0 end),
......
from atable
begin end
else if
begin end
.....
else
不好意思。可能是我刚才没有表达清楚. 我需要的是那5个SELECT WHERE条件中选择如果符合一项条件,就统计.赋值给@tcount(不是@tount0,@tcount1,@tcount2,@tcount3,@tcount4),然后返回.不管下一条件是否符合条件,就不再统计了.用CASE可以满足,可具体怎么用,好象有点复杂。
不好意思。可能是我刚才没有表达清楚. 我需要的是那5个SELECT WHERE条件中选择如果符合一项条件,就统计.赋值给@tcount(不是@tount0,@tcount1,@tcount2,@tcount3,@tcount4),然后返回.不管下一条件是否符合条件,就不再统计了.用CASE可以满足,可具体怎么用,好象有点复杂。
--為什麼不用一個變量,條件做成活動的
select @tcount=count(*) from atable where +條件