Mysql，SubQuery问题

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，而不是使用。

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“