如何在一对多关系中获取最新关联记录（Oracle/PostgreSQL兼容）

现有存储一对多关系的USER_PROFILE表，结构与数据如下：

+----+---------+------------+------------------------+
| ID | USER_ID | PROFILE_ID | LAST_PROFILE_DATE_TIME |
+----+---------+------------+------------------------+
|  1 |     100 |        101 | 04.06.23 08:35:19.5393 |
|  2 |     100 |        102 | 05.06.23 08:35:19.5393 |
+----+---------+------------+------------------------+

需获取用户100的最新PROFILE_ID，预期结果为：

+------------+
| PROFILE_ID |
+------------+
|        102 |
+------------+

用户尝试的SQL因GROUP BY子句限制返回两行：

SELECT up.PROFILE_ID
FROM
  (SELECT USER_ID, PROFILE_ID, MAX(LAST_PROFILE_DATE_TIME)
     FROM USER_PROFILE
    GROUP BY USER_ID, PROFILE_ID) up
WHERE up.USER_ID = 100;

方法一：窗口函数实现（推荐） #

使用ROW_NUMBER()窗口函数按时间倒序排序，筛选出目标用户的第一条记录，该写法同时兼容Oracle和PostgreSQL：

SELECT PROFILE_ID
FROM (
    SELECT 
        USER_ID, 
        PROFILE_ID,
        ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY LAST_PROFILE_DATE_TIME DESC) AS rn
    FROM USER_PROFILE
    WHERE USER_ID = 100
) t
WHERE rn = 1;

• 若存在多条同一时间的最新记录，且需要返回所有匹配项，可将ROW_NUMBER()替换为RANK()。

方法二：关联子查询实现 #

先通过子查询获取目标用户的最新时间，再关联原表匹配对应的PROFILE_ID：

SELECT up.PROFILE_ID
FROM USER_PROFILE up
WHERE up.USER_ID = 100
AND up.LAST_PROFILE_DATE_TIME = (
    SELECT MAX(LAST_PROFILE_DATE_TIME)
    FROM USER_PROFILE
    WHERE USER_ID = 100
);

• 该写法同样兼容两种数据库，若存在多条同时间的最新记录，会返回所有符合条件的PROFILE_ID。

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