Oracle LAG函数使用疑问：如何获取符合日期条件的上一个程序？

我懂你遇到的问题了——LAG()函数确实只会按你指定的排序规则取前一行，完全不会考虑日期重叠的情况。想要拿到真正有效的上一个程序（结束日期不晚于当前程序开始日期），得换个思路，下面给你几个实用的方案，适配不同的数据库场景：

方案1：关联子查询（通用所有数据库） #

如果你的数据量不算特别大，这个通用方案最稳妥：对每一行，直接查询同分组（比如同一个用户/业务单元）里，结束日期≤当前程序开始日期的所有程序中，结束日期最晚的那一个，就是你要的上一个有效程序。

示例代码：

SELECT 
    a.*,
    -- 替换program_name为你要获取的上一个程序字段
    (SELECT program_name 
     FROM TableA b
     -- 如果有分组维度（比如用户ID），加上这行；没有就删除
     WHERE b.group_id = a.group_id 
       AND b.end_date <= a.start_date
       AND b.program_id != a.program_id -- 排除当前程序本身
     ORDER BY b.end_date DESC
     LIMIT 1) AS previous_valid_program
FROM TableA a;

提示：如果是Oracle数据库，把LIMIT 1换成FETCH FIRST 1 ROW ONLY；如果是SQL Server，换成TOP 1。

方案2：窗口函数+条件判断（适合中小数据量） #

先通过LAG()获取前一行的程序和结束日期，再用CASE语句过滤不符合日期条件的记录。如果前一行不符合，就返回NULL；如果需要连续跳过不符合的记录，这个方案可能不够，但适合大多数基础场景。

示例代码：

WITH ordered_programs AS (
    SELECT 
        *,
        -- 获取前一行的程序名称和结束日期
        LAG(program_name) OVER (PARTITION BY group_id ORDER BY start_date) AS lag_program,
        LAG(end_date) OVER (PARTITION BY group_id ORDER BY start_date) AS lag_end_date
    FROM TableA
)
SELECT 
    *,
    -- 只保留符合日期条件的前序程序
    CASE WHEN lag_end_date <= start_date THEN lag_program ELSE NULL END AS previous_valid_program
FROM ordered_programs;

方案3：递归CTE（处理连续不符合的场景） #

如果存在多个前序程序，其中有些结束日期晚于当前程序的开始日期，需要跳过这些无效记录，一直找到最早的有效程序，递归CTE是最佳选择：

示例代码：

WITH RECURSIVE ranked_programs AS (
    -- 先给同分组的程序按开始日期排序
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY start_date) AS rn
    FROM TableA
),
previous_programs AS (
    -- 递归起点：第一个程序没有前序程序
    SELECT 
        rn,
        program_id,
        program_name,
        start_date,
        end_date,
        CAST(NULL AS VARCHAR) AS previous_valid_program
    FROM ranked_programs
    WHERE rn = 1
    UNION ALL
    -- 递归逻辑：依次匹配下一个程序，判断前序是否有效
    SELECT 
        curr.rn,
        curr.program_id,
        curr.program_name,
        curr.start_date,
        curr.end_date,
        -- 如果前一个程序有效就用它，否则继承更早的有效程序
        CASE WHEN prev.end_date <= curr.start_date THEN prev.program_name ELSE previous_programs.previous_valid_program END
    FROM ranked_programs curr
    JOIN previous_programs prev ON curr.rn = prev.rn + 1 AND curr.group_id = prev.group_id
)
SELECT * FROM previous_programs;

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