如何过滤同一ID下满足日期顺序的订阅数据（不可新建表）

原始数据 #

预期结果 #

约束条件 #

• 数据按最早日期开始排序，日期格式为dd-mm-yyyy
• 无法创建新表来处理日期

技术问题 #

如何在同一id分组下，筛选出满足当前行的start_date晚于上一条保留行的end_date的记录？

解决方案 #

可以用**递归公共表表达式（CTE）**实现，无需创建新表，具体步骤如下：

实现思路 #

1. 先将原始数据按id分组，把字符串格式的日期转为日期类型后，按start_date升序排序，取出每组的初始行（最早开始的记录）。
2. 通过递归迭代，每次从上一轮保留的记录的end_date之后，筛选出start_date晚于该end_date的最早记录，直到没有符合条件的记录为止。

SQL代码示例（MySQL） #

WITH RECURSIVE filtered_records AS (
    -- 初始步骤：获取id=1分组的最早记录
    SELECT 
        行号, id, start_date, end_date,
        STR_TO_DATE(start_date, '%d-%m-%Y') AS start_dt,
        STR_TO_DATE(end_date, '%d-%m-%Y') AS end_dt
    FROM 你的表名
    WHERE id = 1
    ORDER BY start_dt ASC
    LIMIT 1

    UNION ALL

    -- 递归步骤：筛选下一个符合时间条件的最早记录
    SELECT 
        t.行号, t.id, t.start_date, t.end_date,
        STR_TO_DATE(t.start_date, '%d-%m-%Y') AS start_dt,
        STR_TO_DATE(t.end_date, '%d-%m-%Y') AS end_dt
    FROM 你的表名 t
    JOIN filtered_records fr ON t.id = fr.id
    WHERE STR_TO_DATE(t.start_date, '%d-%m-%Y') > fr.end_dt
    ORDER BY start_dt ASC
    LIMIT 1
)
SELECT 行号, id, start_date, end_date FROM filtered_records;

适配其他数据库的日期转换函数 #

• PostgreSQL：替换STR_TO_DATE(xxx, '%d-%m-%Y')为TO_DATE(xxx, 'DD-MM-YYYY')
• SQL Server：替换为CONVERT(DATE, xxx, 105)

处理多id分组的修改 #

如果需要同时处理所有id分组，可将初始步骤修改为：

-- 初始步骤：获取每个id分组的最早记录
SELECT 
    行号, id, start_date, end_date,
    STR_TO_DATE(start_date, '%d-%m-%Y') AS start_dt,
    STR_TO_DATE(end_date, '%d-%m-%Y') AS end_dt
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY STR_TO_DATE(start_date, '%d-%m-%Y') ASC) AS rn
    FROM 你的表名
) t
WHERE rn = 1

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