Oracle SQL如何创建循环比较日期间隔标记符合资格的报名记录

你需要的逐行更新参照日期的资格校验逻辑，在Oracle中可以直接通过SQL实现，无需编写PL/SQL循环，以下是两种适配不同版本的实现方案：

前置准备：构造测试样例 #

-- 创建测试表
CREATE TABLE signup_records (
    id NUMBER, -- 人员唯一标识
    signup_date DATE -- 报名日期
);

-- 插入样例数据
INSERT INTO signup_records VALUES (1, DATE '2021-01-01');
INSERT INTO signup_records VALUES (1, DATE '2021-02-01');
INSERT INTO signup_records VALUES (1, DATE '2021-03-01');
INSERT INTO signup_records VALUES (1, DATE '2021-04-01');
INSERT INTO signup_records VALUES (1, DATE '2021-05-01');
INSERT INTO signup_records VALUES (1, DATE '2021-06-01');
COMMIT;

方案1：Oracle 12c及以上版本（MATCH_RECOGNIZE模式匹配） #

该方案是Oracle专门为行级模式匹配场景提供的原生语法，性能最优，代码最简洁：

SELECT 
    id,
    signup_date,
    CASE WHEN matched = 1 THEN '有效' ELSE '无效' END AS qualification_flag
FROM signup_records
MATCH_RECOGNIZE (
    PARTITION BY id -- 按人员ID分组，每人单独计算资格
    ORDER BY signup_date -- 按报名时间升序排列
    MEASURES COUNT(valid.signup_date) AS matched
    ALL ROWS PER MATCH
    PATTERN (valid invalid*) -- 匹配规则：1个有效报名后跟N个无效报名
    DEFINE 
        -- 无效报名判定：距离当前组第一个有效报名不足40天
        invalid AS signup_date < FIRST(valid.signup_date) + 40
);

方案2：Oracle 11g及更早版本（递归CTE） #

完全对齐你描述的循环逻辑，兼容性更强：

WITH 
-- 步骤1：给每个人的报名记录按时间排序加行号
ranked_signup AS (
    SELECT 
        id,
        signup_date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY signup_date) AS rn
    FROM signup_records
),
-- 步骤2：递归逐行计算资格和参照日期
recur_calc AS (
    -- 递归起点：每人第一条报名默认有效
    SELECT 
        id,
        signup_date,
        rn,
        '有效' AS qualification_flag,
        signup_date AS ref_date
    FROM ranked_signup
    WHERE rn = 1
    UNION ALL
    -- 递归逻辑：逐行和上一个有效报名日期比对
    SELECT 
        r.id,
        r.signup_date,
        r.rn,
        CASE WHEN r.signup_date >= rc.ref_date + 40 THEN '有效' ELSE '无效' END,
        CASE WHEN r.signup_date >= rc.ref_date + 40 THEN r.signup_date ELSE rc.ref_date END
    FROM ranked_signup r
    JOIN recur_calc rc ON r.id = rc.id AND r.rn = rc.rn + 1
)
SELECT id, signup_date, qualification_flag 
FROM recur_calc
ORDER BY id, signup_date;

运行结果验证 #

两种方案输出完全符合你给出的业务规则：

原表更新方案 #

如果需要把资格标记写入原表，可以用MERGE语句实现：

-- 新增标记字段
ALTER TABLE signup_records ADD qualification_flag VARCHAR2(10);

-- 批量更新标记
MERGE INTO signup_records s
USING (
    -- 把上述递归CTE或MATCH_RECOGNIZE查询语句放在此处即可
    WITH 
    ranked_signup AS (
        SELECT id, signup_date, ROW_NUMBER() OVER (PARTITION BY id ORDER BY signup_date) AS rn FROM signup_records
    ),
    recur_calc AS (
        SELECT id, signup_date, rn, '有效' AS qualification_flag, signup_date AS ref_date FROM ranked_signup WHERE rn = 1
        UNION ALL
        SELECT r.id, r.signup_date, r.rn, CASE WHEN r.signup_date >= rc.ref_date + 40 THEN '有效' ELSE '无效' END, CASE WHEN r.signup_date >= rc.ref_date + 40 THEN r.signup_date ELSE rc.ref_date END
        FROM ranked_signup r JOIN recur_calc rc ON r.id = rc.id AND r.rn = rc.rn + 1
    )
    SELECT id, signup_date, qualification_flag FROM recur_calc
) t
ON (s.id = t.id AND s.signup_date = t.signup_date)
WHEN MATCHED THEN UPDATE SET s.qualification_flag = t.qualification_flag;
COMMIT;

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