Mysql，子查询问题

Question

Select rating_id, average_rating

From (Select rating_id, avg(rating_num) as average_rating
    from ratings
    group by rating_id
    having count(*) > 50)

HAVING average_rating > 4 ;

运行查询后，出现错误

每个派生表必须有自己的别名

我知道这里的部分有效：

Select rating_id, avg(rating_num) as average_rating
        from ratings
        group by rating_id
        having count(*) > 50

我在这个子查询中做错了什么？我找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找啊找不到错误，不管哪里改正，还是有错误

Answer 1

将“as SomeAlias”放在子查询之后：

Select rating_id, average_rating

From (Select rating_id, avg(rating_num) as average_rating
    from ratings
    group by rating_id
    having count(*) > 50) as A

HAVING average_rating > 4 ;

Answer 2

正如错误消息所述，您需要为子查询添加别名：

SELECT rating_id, average_rating
FROM (
    SELECT
        rating_id,
        AVG(rating_num) AS average_rating
    FROM ratings
    GROUP BY rating_id
    HAVING COUNT(*) > 50
) AS some_alias
WHERE average_rating > 4 

some_alias 可以是任何内容 - 可以是子查询的描述性名称，也可以是因为您永远不需要通过名称引用子查询可以只使用非描述性名称，例如 T1（如果有其他子查询，则使用 T2、T3 等）。

您还可以在外部查询中使用 WHERE 而不是 HAVING。

Answer 3

Select rating_id, average_rating

From (Select rating_id, avg(rating_num) as average_rating
    from ratings
    group by rating_id
    having count(*) > 50) a

HAVING average_rating > 4 ;

注意表别名“a”