SQLite中如何筛选最新日期时间数据并保存至新表？

云上老周

2026-8-20

按ID提取最新日期及对应时间的记录并创建新表

需求：从Table 1中选取每个ID对应的最新日期下的最晚结束时间记录，将这些数据保存到名为Table 2的新表中。

Table 1结构及数据

ID	DATE_1	RUNTIME_1	DATE	ENDTIME_1
1	2021-12-12	10:09:56	2021-12-12	15:09:56
1	2021-12-12	09:09:56	2021-12-12	16:09:56
2	2020-05-22	13:09:12	2021-05-22	22:09:56
2	2020-05-22	09:43:23	2021-05-22	10:09:56
2	2020-05-21	11:43:23	2021-05-22	21:09:56

目标Table 2结构及数据

ID	DATE_1	RUNTIME_1	DATE	ENDTIME_1
1	2021-12-12	09:09:56	2021-12-12	16:09:56
2	2020-05-22	13:09:12	2021-05-22	22:09:56

尝试过的代码

CREATE TABLE table2 AS
SELECT *
FROM table1
WHERE DATE_1>= DATE AND ENDTIME_1>= RUNTIME_1;

解决方案

方法1：使用窗口函数（推荐，兼容多数现代SQL数据库）

窗口函数ROW_NUMBER()可以轻松实现分组排序后取第一条记录的需求，逻辑清晰且性能较好：

CREATE TABLE table2 AS
SELECT t.ID, t.DATE_1, t.RUNTIME_1, t.DATE, t.ENDTIME_1
FROM (
    SELECT *,
           -- 按ID分组，先按DATE降序（取最新日期），再按ENDTIME_1降序（取该日期下最晚结束时间）
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC, ENDTIME_1 DESC) AS rn
    FROM table1
) t
WHERE t.rn = 1;

方法2：关联子查询（兼容旧版MySQL等不支持窗口函数的数据库）

如果你的数据库不支持窗口函数，可以用子查询嵌套的方式实现：

CREATE TABLE table2 AS
SELECT t1.*
FROM table1 t1
WHERE (t1.ID, t1.DATE, t1.ENDTIME_1) IN (
    SELECT ID, DATE, MAX(ENDTIME_1)
    FROM table1
    WHERE DATE = (SELECT MAX(DATE) FROM table1 WHERE ID = t1.ID)
    GROUP BY ID, DATE
);