sql - psql在聚合函数上过滤行

Question

我有下表:

+------+-----------+-------------+--------------+
| year | team_name | player_name | total_points |
+------+-----------+-------------+--------------+
| 1992 | Magic     | Shaq        | 2000         |
+------+-----------+-------------+--------------+
| 1992 | Magic     | Steve Kerr  | 4000         |
+------+-----------+-------------+--------------+
| 1996 | Lakers    | Shaq        | 2300         |
+------+-----------+-------------+--------------+
| 1996 | Lakers    | Magic       | 1000         |
+------+-----------+-------------+--------------+
| 2004 | Heat      | Shaq        | 3000         |
+------+-----------+-------------+--------------+

我正在尝试编写一个查询来查找 Shaq 得分最高的所有球队。

这是我的尝试:

SELECT year, team, MAX(total_points) FILTER(WHERE player_name = 'Shaq'
FROM basketball
GROUP BY year, team;

这显然是不正确的。我认为我错误地使用了 FILTER，因为我无法找到 Shaq 在该队中得分最高的球队

正确的表格应该只有第三行和最后一行

Answer 1

你很接近。您需要一个 HAVING 子句，将球队的年度最高分与 Shaq 的得分进行比较，并且只保留匹配的那些。

SELECT year, team, MAX(total_points)
FROM basketball
GROUP BY year, team
HAVING MAX(total_points) = MAX(total_points) FILTER (WHERE player_name = 'Shaq');