如何用SQL查询每个非唯一ID的最新4条记录？

需求说明：
数据库中每个ID对应多组记录，每组包含通道1-4各一条，需查询出每个ID的**最新一组（4条）**记录。

数据库结构 #

ID (非唯一)
TestTime (datetime)
Channel (取值仅为1、2、3或4)
Data

示例数据 #

ID         TestTime        Channel Data
001   2022-09-14 16:42:00     1    2.5
001   2022-09-14 16:46:00     2    3.5
001   2022-09-14 16:50:00     3    1.5
001   2022-09-14 16:56:00     4    4.5

002   2022-09-13 16:42:00     1    0.5
002   2022-09-13 16:46:00     2    2.5
002   2022-09-13 16:50:00     3    1.5
002   2022-09-13 16:56:00     4    3.5

001   2022-09-12 16:42:00     1    1.5
001   2022-09-12 16:46:00     2    0.5
001   2022-09-12 16:50:00     3    1.5
001   2022-09-12 16:56:00     4    2.5

期望输出 #

ID         TestTime        Channel Data

002   2022-09-13 16:42:00     1    0.5
002   2022-09-13 16:46:00     2    2.5
002   2022-09-13 16:50:00     3    1.5
002   2022-09-13 16:56:00     4    3.5

001   2022-09-14 16:42:00     1    2.5
001   2022-09-14 16:46:00     2    3.5
001   2022-09-14 16:50:00     3    1.5
001   2022-09-14 16:56:00     4    4.5

解决方案 #

方法1：使用窗口函数（适用于MySQL 8+/SQL Server/PostgreSQL等支持窗口函数的数据库） #

通过窗口函数按ID分组，对每组内的记录按时间倒序、通道正序排序，筛选出每个ID的前4条最新记录：

SELECT ID, TestTime, Channel, Data
FROM (
    SELECT 
        ID, TestTime, Channel, Data,
        ROW_NUMBER() OVER (
            PARTITION BY ID 
            ORDER BY TestTime DESC, Channel ASC
        ) AS rn
    FROM your_table_name
) t
WHERE rn <= 4
ORDER BY ID, Channel;

方法2：先获取每个ID的最新时间（兼容低版本MySQL） #

先找出每个ID对应的最大测试时间，再关联原表取出该时间点的4条记录：

SELECT t1.ID, t1.TestTime, t1.Channel, t1.Data
FROM your_table_name t1
JOIN (
    SELECT ID, MAX(TestTime) AS latest_time
    FROM your_table_name
    GROUP BY ID
) t2 ON t1.ID = t2.ID AND t1.TestTime = t2.latest_time
ORDER BY t1.ID, t1.Channel;

注：如果同一ID在同一时间点存在超过4条记录（不符合题目每组4条的前提），方法2可能返回多余数据，此时优先使用方法1。

内容的提问来源于stack exchange，提问作者Anthony The Nguyen