Oracle 21C查询：按UserId限制最多返回3条最新记录

需求说明 #

给定表中包含用户的多条记录，要求查询时每个UserId最多返回3条记录；若该用户记录超过3条，则返回按Date降序排序后的最新3条。根据示例数据，最终应返回10条记录：UserId1、2各3条最新记录，UserId3返回2条，UserId4、5各返回1条。

解决方案SQL语句 #

SELECT UserId, Date, Objective, Value
FROM (
    SELECT 
        UserId, 
        Date, 
        Objective, 
        Value,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) AS rn
    FROM your_table_name
) t
WHERE rn <= 3;

语句解释 #

1. 内层子查询使用ROW_NUMBER()窗口函数：
   • PARTITION BY UserId：按用户ID分组，为每个用户单独计算行号
   • ORDER BY Date DESC：在每个用户组内，按日期从新到旧排序，最新的记录行号为1
2. 外层查询筛选出行号rn <= 3的记录，实现每个用户最多返回3条最新数据的需求。

结果验证 #

执行该语句后：

• UserId1的最新3条：2022-10-19、2022-06-19、2022-05-19的记录
• UserId2的最新3条：2022-07-19、2022-06-09、2022-05-05的记录
• UserId3的2条记录：2022-09-19、2022-02-19的记录
• UserId4、5各返回1条自身唯一的记录
  总计3+3+2+1+1=10条，完全符合需求。

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