where是在分组(group by)前筛选
having是分组后的筛选条件,在分组后的数据组内再筛选
例题:牛客入门篇SQL19
描述
题目:现在运营想查看每个学校用户的平均发贴和回帖情况,寻找低活跃度学校进行重点运营,请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
错误写法:
select
university,
avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
where avg(question_cnt)<5 or avg(answer_cnt)<20
正确写法:
select
university,
avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg(question_cnt)<5 or avg(answer_cnt)<20
或者将最后一句改成
having avg_question_cnt <5 or avg_answer_cnt <20