时间戳

首页 > 解决方案 > 它说的 SQL 代码在 (MAX(AVG(winRate))) 中不能有聚合函数

sql - 它说的 SQL 代码在 (MAX(AVG(winRate))) 中不能有聚合函数

问题描述

SELECT Teams_ID
FROM Players P, Heros H
WHERE P.Heros_ID = H.ID
GROUP BY Teams_ID
HAVING AVG(winRate)=(
    SELECT MAX(AVG(winRate))
    FROM Players P, Heros H
    WHERE P.Heros_ID = H.ID
    GROUP BY Teams_ID
);

标签： sql

解决方案

您必须将其编写为两个单独的SELECT子句：

SELECT Teams_ID
FROM Players P, Heros H
WHERE P.Heros_ID = H.ID
GROUP BY Teams_ID
HAVING AVG(winRate)=(
    SELECT MAX(avgWinRate)
    FROM (SELECT AVG(winRate) as avgWinRate
          FROM Players P, Heros H
          WHERE P.Heros_ID = H.ID
          GROUP BY Teams_ID
    ) as t
);

这清楚地表明 theMAX 并不意味着GROUP BYing Teams_ID。

您可能还希望 a) 查看显式连接语法 b) 如果您的 RDBMS 支持它，请查看窗口聚合以避免重复查询。

如果您的数据库支持，这就是我更喜欢的：

select *
from (
    select *,ROW_NUMBER() OVER (ORDER BY AvgWinRate desc) rn
    from (
        SELECT Teams_ID,AVG(winRate) OVER (PARTITION BY Teams_ID)  as AvgWinRate
        FROM Players P inner join Heros H
        ON P.Heros_ID = H.ID
        ) t
    ) u
where rn = 1

这也更自然地扩展/组合更大的查询。

推荐阅读