本文介绍了一种使用CASE WHEN语句优化SQL查询的方法,通过将COUNT(*)替换为SUM(CASE WHEN),可以更灵活地针对特定条件计数并同时获取多个统计数据。
原SQL:select count(*) as total from jpy t where t.jb='987'
修改后:
select
sum(case t.jb when '987' then 1 else 0 end) as total
from
jpy t
SQL优化例子:
select
sum(case t.jb when 987 then 1 else 0 end) as count1,
sum(case t.jb when 988 then 1 else 0 end) as count2,
sum(case t.jb when 989 then 1 else 0 end) as count3,
sum(case t.jb when 1104 then 1 else 0 end) as count4,
count(t.jb) as counTotal
from jpy t
修改后:
select
sum(case t.jb when '987' then 1 else 0 end) as total
from
jpy t
SQL优化例子:
select
sum(case t.jb when 987 then 1 else 0 end) as count1,
sum(case t.jb when 988 then 1 else 0 end) as count2,
sum(case t.jb when 989 then 1 else 0 end) as count3,
sum(case t.jb when 1104 then 1 else 0 end) as count4,
count(t.jb) as counTotal
from jpy t
扫一扫
1014
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
