PostgreSQL如何获取多列MAX值及对应行的其他关联字段

原思路问题说明 #

你原本的两次查询方案需要额外处理最大值多匹配行的筛选逻辑，还会增加数据库往返开销，以下两种直接在SQL层完成的方案效率更高、逻辑更简洁。

方案1：UNION ALL 逐字段查询（推荐） #

该方案逻辑清晰易维护，每个字段的查询可独立命中对应字段的索引，20个字段都建索引的前提下，千万级表也能毫秒级返回结果，且默认处理了最大值重复取ID最小的第一条的需求。

表格格式输出SQL #

SELECT * FROM (
  -- 击杀数最大值对应行
  SELECT 'kills' AS what, kills AS amount, gamemode, id
  FROM matches 
  ORDER BY kills DESC, id ASC
  LIMIT 1
) t_kills
UNION ALL
SELECT * FROM (
  -- 死亡数最大值对应行
  SELECT 'deaths' AS what, deaths AS amount, gamemode, id
  FROM matches 
  ORDER BY deaths DESC, id ASC
  LIMIT 1
) t_deaths
UNION ALL
SELECT * FROM (
  -- 助攻数最大值对应行
  SELECT 'assists' AS what, assists AS amount, gamemode, id
  FROM matches 
  ORDER BY assists DESC, id ASC
  LIMIT 1
) t_assists;

JSON格式输出SQL #

SELECT json_build_object(
  'maxKills', (SELECT json_build_object('id', id, 'kills', kills, 'gamemode', gamemode) FROM matches ORDER BY kills DESC, id ASC LIMIT 1),
  'maxDeaths', (SELECT json_build_object('id', id, 'deaths', deaths, 'gamemode', gamemode) FROM matches ORDER BY deaths DESC, id ASC LIMIT 1),
  'maxAssists', (SELECT json_build_object('id', id, 'assists', assists, 'gamemode', gamemode) FROM matches ORDER BY assists DESC, id ASC LIMIT 1)
) AS result;

扩展到20个字段只需按照上述格式新增对应查询块即可，修改排序字段和返回标识即可。

方案2：窗口函数单表扫描 #

如果你的表没有给对应字段建索引，且数据量不大的情况下，可以用窗口函数实现仅扫描一次表得到结果：

WITH ranked_matches AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (ORDER BY kills DESC, id ASC) AS rn_kills,
    ROW_NUMBER() OVER (ORDER BY deaths DESC, id ASC) AS rn_deaths,
    ROW_NUMBER() OVER (ORDER BY assists DESC, id ASC) AS rn_assists
  FROM matches
)
SELECT 'kills' AS what, kills AS amount, gamemode, id FROM ranked_matches WHERE rn_kills = 1
UNION ALL
SELECT 'deaths' AS what, deaths AS amount, gamemode, id FROM ranked_matches WHERE rn_deaths = 1
UNION ALL
SELECT 'assists' AS what, assists AS amount, gamemode, id FROM ranked_matches WHERE rn_assists = 1;

自定义调整说明 #

• 如果最大值重复时不需要取ID最小的行，只需修改排序规则，比如按gamemode DESC排序即可
• 如果需要保留所有最大值匹配的行，把LIMIT 1去掉，或者把ROW_NUMBER换成RANK函数即可

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