记一次子查询优化，where与having优先级问题

原始sql

SELECT b.school_id , COUNT(school_id)  shuliang FROM Sheet1 a join tag b ON a.学校编号=b.school_id GROUP BY school_id where COUNT(school_id)<90;

报错 ！

原因：

大致解释如下，sql语句的执行过程是：from-->where-->group by -->having --- >order by --> select;
聚合函数是针对结果集进行的，但是where条件并不是在查询出结果集之后运行，所以主函数放在where语句中，会出现错误，

而having不一样，having是针对结果集做筛选的，所以我门一般吧组函数放在having中，用having来代替where，having一般跟在group by后

优化经历

第一版：将结果放入子查询实现where查询

SELECT school_id,shuliang from (  SELECT b.* , COUNT(school_id)  shuliang FROM Sheet1 a join tag b ON a.学校编号=b.school_id GROUP BY school_id ) t WHERE shuliang<90 ;

第二版：使用having

SELECT b.*，COUNT(school_id) shuliang FROM Sheet1 a JOIN tag b ON a.学校编号 = b.school_id GROUP BY b.school_id HAVING shuliang < 90; 

第三版：简化代码

SELECT b.school_id , COUNT(school_id)  shuliang FROM Sheet1 a join tag b ON a.学校编号=b.school_id GROUP BY school_id HAVING COUNT(school_id)<90;

附上执行计划：

从第一版到最终sql 性能大概提升了40%