如何在SQL中按date_diff≥3个月筛选目标行号？

嘿，这个需求刚好能用SQL里的递归CTE来解决！这是典型的需要迭代筛选的场景，咱们一步步来拆解实现思路：

首先明确你的核心规则：固定提取首行（row_number=1），之后每一步都以上一次选中的行的date为基准，筛选出与该日期相差至少3个月的行，重复这个过程直到没有符合条件的行。这种循环迭代的逻辑，递归公共表表达式（CTE）就是专门干这个的！

假设你的row_number已按用户+日期升序排好 #

如果表中的row_number已经是每个用户按date从小到大排好的序号，直接用下面的递归CTE即可：

WITH RECURSIVE selected_rows AS (
    -- 锚点成员：递归的起点，抓取每个用户的首行
    SELECT id, row_number, User, date
    FROM users
    WHERE row_number = 1

    UNION ALL

    -- 递归成员：以上一轮选中的行为基准，筛选符合条件的下一行
    SELECT u.id, u.row_number, u.User, u.date
    FROM users u
    JOIN selected_rows sr 
      ON u.User = sr.User -- 按用户分组独立处理，避免跨用户干扰
    WHERE u.row_number > sr.row_number -- 只找当前行之后的记录
      -- 根据你的数据库替换日期差函数：
      -- MySQL用 DATEDIFF(u.date, sr.date) >= 90（按天计算3个月）
      -- Oracle用 MONTHS_BETWEEN(u.date, sr.date) >= 3
      -- PostgreSQL用 AGE(u.date, sr.date) >= INTERVAL '3 months'
      AND MONTHS_BETWEEN(u.date, sr.date) >= 3
      -- 加这个子查询确保每次只选最早符合条件的行，严格遵循你的迭代规则
      AND u.row_number = (
          SELECT MIN(row_number)
          FROM users
          WHERE User = sr.User
            AND row_number > sr.row_number
            AND MONTHS_BETWEEN(date, sr.date) >= 3
      )
)
-- 最终输出所有选中的行，按用户和行号排序
SELECT * FROM selected_rows ORDER BY User, row_number;

如果表中没有预先排好的row_number #

如果你的row_number不是按日期排序的，需要先给每个用户的记录生成正确的行号，再进行递归筛选：

-- 第一步：给每个用户按日期生成有序的行号
WITH ranked_users AS (
    SELECT id, User, date,
           ROW_NUMBER() OVER (PARTITION BY User ORDER BY date) AS row_number
    FROM users
),
-- 第二步：递归筛选符合条件的行
recursive selected_rows AS (
    SELECT id, row_number, User, date
    FROM ranked_users
    WHERE row_number = 1

    UNION ALL

    SELECT ru.id, ru.row_number, ru.User, ru.date
    FROM ranked_users ru
    JOIN selected_rows sr 
      ON ru.User = sr.User
    WHERE ru.row_number > sr.row_number
      AND AGE(ru.date, sr.date) >= INTERVAL '3 months' -- PostgreSQL示例，替换成你数据库的函数
      AND ru.row_number = (
          SELECT MIN(row_number)
          FROM ranked_users
          WHERE User = sr.User
            AND row_number > sr.row_number
            AND AGE(date, sr.date) >= INTERVAL '3 months'
      )
)
SELECT * FROM selected_rows ORDER BY User, row_number;

逻辑说明 #

• 锚点成员：作为递归的起始点，固定选中每个用户的首行。
• 递归成员：每次和上一轮选中的行做关联，找到该用户中满足「行号更大、日期差至少3个月」的最早行，将其加入结果集后，再以这新行作为基准继续筛选，直到没有符合条件的行时，递归自动停止。

如果你的需求是一次性提取所有符合条件的行（而非每次只取最早的），可以去掉递归成员里的MIN(row_number)子查询，同时加上NOT EXISTS条件避免重复选中已在结果集里的行。

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