如何在PostgreSQL中用子查询筛选多列均为最大值的条目？

现有赛马数据库，已关联horse_ratings与horses两张表，需实现以下需求：

• 仅筛选出单场比赛内同时拥有lastrace_rating和going_rating最大值的条目
• 若某场比赛中没有马匹同时在这两项评分中排名第一，则返回空结果（每场比赛最多一条结果）

尝试过以下查询语句，但未得到预期结果：

SELECT h.racename, h.racetime, h.racedate, h.odds, h.pos
FROM horse_ratings hr
RIGHT JOIN horses h ON hr.daily_racename = h.racename
  AND hr.daily_racedate = h.racedate
  AND hr.daily_racetime = h.racetime
  AND hr.dailyhorsename = h.horsename
WHERE 
  hr.daily_racedate = '2023-04-13'
  AND hr.daily_racename = 'Staropramen Zero Alcohol Handicap'
  AND hr.daily_racetime = '4:15'
  AND final_horse_rating > 0
  AND h.odds > 0
  AND hr.dailyhorsename IS NOT NULL 
  AND hr.lastrace_rating = (SELECT MAX(lastrace_rating) FROM horse_ratings)
  AND hr.going_rating = (SELECT MAX(going_rating) FROM horse_ratings);

即使仅保留单个条件（如going_rating）也无结果，尽管数据确实存在（已通过ORDER BY lastrace_rating DESC验证）。还尝试过以下变体，但思路有误：

AND (hr.lastrace_rating, hr.going_rating) IN (
    SELECT MAX(lastrace_rating), MAX(going_rating)
    FROM horse_ratings

示例表结构 #

horse_ratings表

horses表

期望从上述示例中选出Little Tiger，因为它同时拥有该场比赛内lastrace_rating和going_rating的最大值。需在PostgreSQL中实现该查询。

核心问题在于：之前的子查询是取全表的lastrace_rating和going_rating最大值，而需求是取单场比赛内的最大值。需要将子查询的范围限定到当前比赛（通过daily_racedate、daily_racetime、dailyracename关联）。

方法一：使用关联子查询 #

SELECT h.racename, h.racetime, h.racedate, h.odds, h.pos, h.horsename
FROM horse_ratings hr
JOIN horses h 
  ON hr.daily_racename = h.racename
  AND hr.daily_racedate = h.racedate
  AND hr.daily_racetime = h.racetime
  AND hr.dailyhorsename = h.horsename
WHERE 
  -- 筛选目标比赛（可根据需要调整）
  hr.daily_racedate = '2023-05-23'
  AND hr.daily_racename = 'At The Races App Handicap'
  AND hr.daily_racetime = '05:15:00'
  AND hr.final_horse_rating > 0
  AND h.odds > 0
  -- 限定当前比赛内的双最大值
  AND hr.lastrace_rating = (
    SELECT MAX(lastrace_rating) 
    FROM horse_ratings
    WHERE daily_racedate = hr.daily_racedate
      AND daily_racetime = hr.daily_racetime
      AND dailyracename = hr.dailyracename
  )
  AND hr.going_rating = (
    SELECT MAX(going_rating) 
    FROM horse_ratings
    WHERE daily_racedate = hr.daily_racedate
      AND daily_racetime = hr.daily_racetime
      AND dailyracename = hr.dailyracename
  );

方法二：使用窗口函数（更高效，适合批量查询多场比赛） #

如果需要同时查询多场比赛，窗口函数可以一次性计算每场比赛的最大值，性能更优：

WITH race_max_ratings AS (
  SELECT 
    daily_racedate,
    daily_racetime,
    dailyracename,
    MAX(lastrace_rating) OVER (PARTITION BY daily_racedate, daily_racetime, dailyracename) AS max_lastrace,
    MAX(going_rating) OVER (PARTITION BY daily_racedate, daily_racetime, dailyracename) AS max_going,
    dailyhorsename,
    final_horse_rating
  FROM horse_ratings
)
SELECT h.racename, h.racetime, h.racedate, h.odds, h.pos, h.horsename
FROM race_max_ratings rmr
JOIN horses h 
  ON rmr.daily_racename = h.racename
  AND rmr.daily_racedate = h.racedate
  AND rmr.daily_racetime = h.racetime
  AND rmr.dailyhorsename = h.horsename
WHERE 
  -- 筛选目标比赛（可移除该条件以查询所有比赛）
  rmr.daily_racedate = '2023-05-23'
  AND rmr.daily_racename = 'At The Races App Handicap'
  AND rmr.daily_racetime = '05:15:00'
  AND rmr.final_horse_rating > 0
  AND h.odds > 0
  -- 匹配双最大值
  AND rmr.lastrace_rating = rmr.max_lastrace
  AND rmr.going_rating = rmr.max_going;

关键说明 #

1. 替换了RIGHT JOIN为INNER JOIN：因为需要同时存在于两张表的记录，RIGHT JOIN会保留horses中无对应ratings的记录，不符合需求。
2. 子查询/窗口函数的PARTITION BY限定了比赛的唯一标识（日期、时间、赛事名），确保取的是单场比赛内的最大值，而非全表最大值。
3. 示例中已针对测试数据调整了筛选条件，执行后会返回Little Tiger的记录。

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