Oracle关联表查询如何仅获取指定日期的最新更新记录？

问题根源 #

• 原LEFT JOIN被WHERE子句中的cal_name = 'REGISTRED'条件转为了内连接，会过滤掉没有匹配CALENDAR记录的UNIVERSITIES行
• 分组逻辑存在漏洞：cal_status字段未纳入GROUP BY也未加聚合函数，同一uni_id下如果不同更新时间的cal_status不一致，会导致分组后出现重复行
• 注意原JOIN条件中的unı_id是特殊字符（土耳其语i），大概率是拼写错误，应该为uni_id，拼写错误也可能引发匹配异常

推荐解决方案 #

方案1：窗口函数法（适配所有支持SQL:2003标准的现代数据库，如MySQL8.0+、PostgreSQL、Oracle、SQL Server等） #

SELECT u.uni_id, u.uni_teach_name, c.cal_update, c.cal_status
FROM UNIVERSITIES u
LEFT JOIN (
    SELECT 
        cal_id, cal_update, cal_status,
        -- 按更新时间倒序给同一cal_id的记录排序，最新的排第一
        ROW_NUMBER() OVER (PARTITION BY cal_id ORDER BY cal_update DESC) AS rn
    FROM CALENDAR
    WHERE cal_name = 'REGISTRED'
) c ON u.uni_id = c.cal_id AND c.rn = 1 -- 只取每个cal_id最新的1条记录
WHERE u.uni_master = 'SO88'
  AND u.uni_stat = 'OK'
ORDER BY c.cal_update;

方案2：子查询关联法（兼容MySQL5.x等不支持窗口函数的旧版本数据库） #

SELECT u.uni_id, u.uni_teach_name, c.max_cal_update, c2.cal_status
FROM UNIVERSITIES u
LEFT JOIN (
    -- 先查出每个cal_id的最新更新时间
    SELECT cal_id, MAX(cal_update) AS max_cal_update
    FROM CALENDAR
    WHERE cal_name = 'REGISTRED'
    GROUP BY cal_id
) c ON u.uni_id = c.cal_id
-- 再关联回CALENDAR表拿到最新时间对应的其他字段
LEFT JOIN CALENDAR c2 
    ON c.cal_id = c2.cal_id 
    AND c.max_cal_update = c2.cal_update 
    AND c2.cal_name = 'REGISTRED'
WHERE u.uni_master = 'SO88'
  AND u.uni_stat = 'OK'
ORDER BY c.max_cal_update;

说明 #

以上两种方案都可以保证每个UNIVERSITIES表中符合条件的行仅对应最多1条CALENDAR记录，最终返回的记录数和你第一条单表查询的100条完全一致。如果不需要保留无CALENDAR匹配的记录，把LEFT JOIN换成INNER JOIN即可。

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