PostgreSQL查询：获取每个科目最近的即将到来场次

需求说明 #

现有subject_master（科目表）和slot_master（场次表），一个科目对应多个场次。需编写查询返回每个活跃科目最近的即将到来的唯一场次，当前时间为2022-07-30 05:00，结果需包含subject_id、subject_name、start_time、date字段。

表结构回顾 #

• subject_master：subject_id（科目ID）、subject_name（科目名称）、status（状态）
• slot_master：slot_id（场次ID）、subject_id（关联科目ID）、date（场次日期）、start_time（场次开始时间）

解决方案 #

方法1：使用PostgreSQL专属DISTINCT ON（简洁高效）

DISTINCT ON可按指定字段去重，保留每组的第一条记录，适合单字段分组取极值的场景：

SELECT DISTINCT ON (sm.subject_id)
    sm.subject_id,
    sm.subject_name,
    sl.start_time,
    sl.date
FROM subject_master sm
JOIN slot_master sl ON sm.subject_id = sl.subject_id
WHERE 
    sm.status = 'active'
    -- 拼接日期与时间得到完整场次时间，筛选晚于当前时间的场次
    AND (sl.date + sl.start_time) > '2022-07-30 05:00'::timestamp
-- 按科目分组后，按场次时间升序排序，确保取最近的场次
ORDER BY sm.subject_id, (sl.date + sl.start_time) ASC;

方法2：使用窗口函数ROW_NUMBER()（通用兼容）

若需兼容其他数据库或处理复杂排序逻辑，窗口函数是更通用的选择：

WITH ranked_slots AS (
    SELECT 
        sm.subject_id,
        sm.subject_name,
        sl.start_time,
        sl.date,
        -- 按科目分组，按场次时间升序排名，最近场次排名为1
        ROW_NUMBER() OVER (
            PARTITION BY sm.subject_id 
            ORDER BY (sl.date + sl.start_time) ASC
        ) AS slot_rank
    FROM subject_master sm
    JOIN slot_master sl ON sm.subject_id = sl.subject_id
    WHERE 
        sm.status = 'active'
        AND (sl.date + sl.start_time) > '2022-07-30 05:00'::timestamp
)
SELECT subject_id, subject_name, start_time, date
FROM ranked_slots
WHERE slot_rank = 1;

关键说明 #

• 假设slot_master的date为date类型、start_time为time类型，两者相加可得到完整的timestamp类型场次时间；若字段存储格式不同，需调整时间拼接逻辑（如字符串拼接后转timestamp）。
• 仅筛选status为active的科目，符合常规业务逻辑。
• 两种方法均保证每个科目仅返回一条最近的即将到来场次记录。

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