SQL按Id分组时如何获取level值最大的对应行记录

直接用MAX()搭配GROUP BY只能计算出每个ID对应的最大level值，无法自动关联到该最大值对应的其他字段（比如需要查询的score），这是之前查询结果不符合预期的核心原因。以下是两种可直接运行的实现方式：

方法1：窗口函数实现（推荐，支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库新版本） #

用窗口函数给每个ID分组内的记录按level倒序打排名，取每个组排名为1的记录即可，逻辑清晰易维护：

WITH temp_ranked AS (
  SELECT
    id,
    score,
    -- 替换成你实际计算level的业务逻辑即可
    CASE
      WHEN score = '[]' THEN 0
      ELSE LENGTH(score) - LENGTH(REPLACE(score, ',', '')) + 1
    END AS level,
    ROW_NUMBER() OVER (
      PARTITION BY id
      ORDER BY
        CASE
          WHEN score = '[]' THEN 0
          ELSE LENGTH(score) - LENGTH(REPLACE(score, ',', '')) + 1
        END DESC
      -- 同level有多条记录时可补充排序字段，比如 ORDER BY level DESC, create_time DESC 保证结果稳定
    ) AS row_rank
  FROM your_table
)
SELECT id, score, level
FROM temp_ranked
WHERE row_rank = 1;

方法2：关联子查询实现（兼容不支持窗口函数的旧版本数据库，如MySQL 5.x） #

先聚合算出每个ID对应的最大level，再关联原表筛选出level等于最大值的记录：

SELECT
  t.id,
  t.score,
  CASE
    WHEN t.score = '[]' THEN 0
    ELSE LENGTH(t.score) - LENGTH(REPLACE(t.score, ',', '')) + 1
  END AS level
FROM your_table t
INNER JOIN (
  SELECT
    id,
    MAX(
      CASE
        WHEN score = '[]' THEN 0
        ELSE LENGTH(score) - LENGTH(REPLACE(score, ',', '')) + 1
      END
    ) AS max_level
  FROM your_table
  GROUP BY id
) t_max
ON t.id = t_max.id
AND CASE
      WHEN t.score = '[]' THEN 0
      ELSE LENGTH(t.score) - LENGTH(REPLACE(t.score, ',', '')) + 1
    END = t_max.max_level;

注意事项 #

• 如果你的业务场景允许同一个ID下存在多条level同为最大值的记录，可以把方法1里的ROW_NUMBER()替换为RANK()，即可保留所有符合最大level的行。
• 非严格SQL模式下直接写SELECT id, score, MAX(level) FROM table GROUP BY id返回的score是分组内随机取值，不匹配最大level对应的行，这是SQL语法的默认逻辑，不属于函数bug。
• 计算level的CASE表达式可以抽成子查询别名简化写法，避免重复书写相同逻辑；不同数据库计算数组长度的函数有差异，替换成对应数据库的语法即可。

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