欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-06 21:59:56
...
原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'
相关标签: SQL