含MAX与GROUP BY的子查询问题：获取各Word最高Score数据

原查询问题 #

用户尝试使用以下SQL查询，但无法实现获取每个Word对应的最高Score的所有行的需求：

WHERE Table1.Score = 
    (
    SELECT MAX(Table1.Score) 
    FROM Table1 
    WHERE Table1.Date >= '2023-03-01'
        GROUP BY Table1.Word
    )

表结构与数据 #

Table1的结构及数据如下：

用户期望结果：每个Word对应其最高Score的行，即Faye(90)、Jet(95)、Spike(85)、Toby(70)的所有对应行。

原查询错误原因 #

原SQL中子查询通过GROUP BY Table1.Word返回的是多个值（每个Word的最高分），而外层使用=进行匹配，=只能匹配单个值，会导致语法错误或仅返回部分匹配结果，无法实现需求。

解决方案 #

方案1：关联子查询 #

通过关联外层表的Word字段，让子查询针对每个Word单独计算最高分：

SELECT t1.*
FROM Table1 t1
WHERE t1.Date >= '2023-03-01'
  AND t1.Score = (
    SELECT MAX(t2.Score)
    FROM Table1 t2
    WHERE t2.Date >= '2023-03-01'
      AND t2.Word = t1.Word
  );

方案2：窗口函数（推荐） #

利用RANK()窗口函数标记每个Word分组内的最高分记录，若同一Word有多个相同最高分的行，会全部保留：

SELECT Word, Score, Date
FROM (
    SELECT 
        *,
        RANK() OVER (PARTITION BY Word ORDER BY Score DESC) AS rn
    FROM Table1
    WHERE Date >= '2023-03-01'
) sub
WHERE rn = 1;

如果只需每个Word取一条最高分记录，可将RANK()替换为ROW_NUMBER()。

方案3：IN组合匹配 #

通过(Word, Score)组合匹配子查询返回的每个Word的最高分，避免不同Word间最高分重复导致的错误：

SELECT t1.*
FROM Table1 t1
WHERE t1.Date >= '2023-03-01'
  AND (t1.Word, t1.Score) IN (
    SELECT Word, MAX(Score)
    FROM Table1
    WHERE Date >= '2023-03-01'
    GROUP BY Word
  );

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