一个sql优化例子:用sum取代count

原创于 2011-03-27 16:40:53 发布 · 318 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

本文介绍了一种通过使用CASE WHEN语句来替代子查询的方法优化SQL查询效率的技术。这种方法可以减少数据库系统的负载，提高查询速度。文章提供了具体的优化前后对比案例，展示了如何在实际场景中应用这一技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原SQL:select count(*) as total from table a where a.row1='0'

修改后:
select
sum(case a.row1 when '0' then 1 else 0 end) as total
from
table a

或则

select
sum(if(a.row1='0',1,0)) as total
from
table a

SQL优化例子:

优化前

select
s.Name,
(select count(*) from CheckCertLog where StoreId=s.StoreId and State=0 and CheckTime between '2010-02-02' and '2010-9-2')as fail_count,
(select count(*) from CheckCertLog where StoreId=s.StoreId and State=1 and CheckTime between '2010-02-02' and '2010-9-2')as success_count,
(select count(*) from CheckCertLog where StoreId=s.StoreId and CheckTime between '2010-02-02' and '2010-9-2')as tcount
from
Store as s

优化后

select
a.Name,
sum(case b.State when 0 then 1 else 0 end ) as fail_count,
sum(case b.State when 1 then 1 else 0 end ) as success_count,
sum(case b.State when 1 then 1 else 1 end) as total
from
Store a, CheckCertLog b
where
a.StoreId=b.StoreId and b.CheckTime between '2010-02-02' and '2010-9-2'