如何通过PostgreSQL查询识别跳过阶段的ID？

看起来你需要找出那些没有按顺序经历所有要求阶段的ID，对吧？根据你的示例数据和描述，每个ID必须依次经过02→03→04→05→06这五个阶段，不能跳过任何中间步骤。下面我给你两种PostgreSQL的实现方案，都能帮你识别出跳过阶段的ID。

首先，先把你的示例数据整理成可测试的表结构（方便你验证）：

CREATE TABLE stages (
    id INT,
    current_stage VARCHAR(2),
    previous_stage VARCHAR(2)
);

INSERT INTO stages VALUES
(1, '06', '05'),
(1, '06', '03'),
(2, '04', '03'),
(2, '04', '02');

方案一：检查阶段范围中的缺失项 #

这个方案会明确找出每个ID缺失的阶段，同时返回跳过阶段的ID：

WITH required_stages AS (
    -- 定义所有必须经过的阶段及顺序
    SELECT stage, row_number() OVER () AS seq
    FROM (VALUES ('02'), ('03'), ('04'), ('05'), ('06')) AS s(stage)
),
id_stages AS (
    -- 收集每个ID的所有经历阶段（当前+历史），去重
    SELECT DISTINCT id, stage
    FROM (
        SELECT id, current_stage AS stage FROM stages
        UNION ALL
        SELECT id, previous_stage AS stage FROM stages
    ) AS all_stages
),
id_max_stage AS (
    -- 获取每个ID当前到达的最高阶段对应的顺序号
    SELECT id, MAX(rs.seq) AS max_seq
    FROM id_stages is_
    JOIN required_stages rs ON is_.stage = rs.stage
    GROUP BY id
),
id_missing_stages AS (
    -- 找出每个ID在已到达阶段范围内缺失的阶段
    SELECT ims.id, rs.stage AS missing_stage
    FROM id_max_stage ims
    CROSS JOIN required_stages rs
    LEFT JOIN id_stages is_ ON is_.id = ims.id AND is_.stage = rs.stage
    WHERE rs.seq <= ims.max_seq
      AND is_.id IS NULL
)
-- 返回所有存在跳过阶段的ID，以及它们缺失的阶段（可选）
SELECT DISTINCT id, missing_stage
FROM id_missing_stages;

执行这个查询后，你会得到ID=1，以及它缺失的阶段02和04，完美符合你的示例描述。

方案二：检查阶段序列的连续性 #

如果你只需要找出有问题的ID，不需要知道具体缺失的阶段，这个更简洁的方案适合你：

WITH required_stages AS (
    SELECT stage, row_number() OVER () AS seq
    FROM (VALUES ('02'), ('03'), ('04'), ('05'), ('06')) AS s(stage)
),
id_ordered_stages AS (
    -- 按顺序排列每个ID的经历阶段
    SELECT id, rs.seq
    FROM (
        SELECT DISTINCT id, stage
        FROM (
            SELECT id, current_stage AS stage FROM stages
            UNION ALL
            SELECT id, previous_stage AS stage FROM stages
        ) AS all_stages
    ) AS is_
    JOIN required_stages rs ON is_.stage = rs.stage
    ORDER BY id, seq
),
id_gaps AS (
    -- 检查相邻阶段的顺序号是否有间隙
    SELECT id,
           seq - LAG(seq) OVER (PARTITION BY id ORDER BY seq) AS gap
    FROM id_ordered_stages
)
-- 找出间隙大于1（中间跳过阶段）或第一个阶段不是初始阶段的ID
SELECT DISTINCT id
FROM id_gaps
WHERE gap > 1
UNION
SELECT id
FROM id_ordered_stages
GROUP BY id
HAVING MIN(seq) > 1;

这个查询会直接返回ID=1，也就是跳过了阶段的ID。

关键思路说明 #

两种方案的核心都是先给每个要求的阶段分配一个顺序号，这样我们可以用数字来判断阶段的连续性：

• 首先收集每个ID的所有经历阶段，去重后和要求的阶段对应起来。
• 要么检查从初始阶段到当前最高阶段之间是否有缺失，要么检查阶段序列的相邻顺序号是否连续。
• 额外处理了“跳过初始阶段”的情况（比如ID=1没有经历02），这种情况也属于不符合要求的跳过。

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