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

最新推荐文章于 2021-09-25 23:22:06 发布

littleant

最新推荐文章于 2021-09-25 23:22:06 发布

阅读量245

点赞数

分类专栏：数据库文章标签： SQL

本文链接：https://blog.csdn.net/littleant678/article/details/83841659

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

原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'