PostgreSQL：筛选Shaq为队内得分最高球队的SQL查询问题

原始数据 #

+------+-----------+-------------+--------------+
| 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         |
+------+-----------+-------------+--------------+

你的尝试问题分析 #

你写的查询存在两个问题：

• 语法错误：FILTER(WHERE player_name = 'Shaq' 缺少闭合的右括号
• 逻辑偏差：仅提取了Shaq的得分，但未将其与队内其他球员的最高得分做对比，无法判断他是否是队内得分最高的球员

正确解法 #

方法一：窗口函数排序筛选 #

利用RANK()窗口函数按年份+球队分组，对组内得分降序排序，再筛选Shaq排名第一的记录：

SELECT year, team_name, player_name, total_points
FROM (
    SELECT 
        *,
        RANK() OVER (PARTITION BY year, team_name ORDER BY total_points DESC) AS score_rank
    FROM basketball
) ranked_data
WHERE player_name = 'Shaq' AND score_rank = 1;

方法二：子查询计算队内最高分 #

先统计每个球队每年的最高得分，再关联原表筛选出Shaq得分等于该最高分的记录：

SELECT b.year, b.team_name, b.player_name, b.total_points
FROM basketball b
INNER JOIN (
    SELECT year, team_name, MAX(total_points) AS max_team_points
    FROM basketball
    GROUP BY year, team_name
) team_score ON b.year = team_score.year 
             AND b.team_name = team_score.team_name
             AND b.total_points = team_score.max_team_points
WHERE b.player_name = 'Shaq';

两种方法都能得到目标结果：1996年湖人队、2004年热火队的Shaq记录。

内容的提问来源于stack exchange，提问作者spotty23544332