mysql - 如何解决 MySQL Left WHERE 条件错误？

Question

下面的语句给出了没有任何错误的结果，

select 
title as 'unreviewed series', 
rating from series 
left join reviews 
    on series.id= reviews.series_id  
where rating is null;

但是，下面给出了一个错误:

select title as 'unreviewed series', 
ifnull(avg(rating), null) as 'Average Rating' 
from series 
left join reviews 
    on series.id= reviews.series_id  
where avg(rating) = null;

mysql给出的错误是在WHERE附近使用了正确的语法。

Answer 1

使用 having 和 group by - where 将不起作用，因为您正在尝试使用聚合函数进行过滤

select title as 'unreviewed series', 
ifnull(avg(rating), null) as 'Average Rating' 
from series 
left join reviews 
    on series.id= reviews.series_id  
group by title
having avg(rating) is null