Q1 记SQL查询的一次优化 where 与 having

原sql

表用table_a, table_b代替，字段简化，sql复杂程度简化。

select a.date, sum(a.num), b.name
from table_a a
    left join table_b b on b.a_orderno = a.orderno
group by a.date, b.name
order by a.date 

数据量较大，无条件过滤，查询效率还过得去，但加上查询条件，查询效率直线下降，毫无头绪。

select a.date, sum(a.num), b.name
from table_a a
    left join table_b b on b.a_orderno = a.orderno
where a.date <= '2020-01-01' 
    and a.date >= '2020-12-31'
group by a.date, b.name
order by a.date

后发现，查询条件均为分组条件，将where改为having，查询效率正常

select a.date, sum(a.num), b.name
from table_a a
    left join table_b b on b.a_orderno = a.orderno
group by a.date, b.name
having a.date <= '2020-01-01' 
    and a.date >= '2020-12-31'
order by a.date