SQL筛选：按顺序选取in_id与out_id均未重复出现的行

你的需求本质是按row_num顺序迭代筛选，仅保留当前行的in_id和out_id均未在已选中行中出现过的记录，普通关联查询无法实现这种依赖动态选中集合的逻辑，需要使用递归CTE（Common Table Expression）追踪已使用的ID集合。

可行SQL查询 #

WITH RECURSIVE selected_rows AS (
    -- 初始选中row_num最小的行，记录已使用的ID集合
    SELECT 
        row_num,
        in_id,
        out_id,
        ARRAY[in_id, out_id] AS used_ids
    FROM candidates
    WHERE row_num = (SELECT MIN(row_num) FROM candidates)
    
    UNION ALL
    
    -- 递归筛选后续符合条件的行
    SELECT 
        c.row_num,
        c.in_id,
        c.out_id,
        sr.used_ids || ARRAY[c.in_id, c.out_id]
    FROM candidates c
    JOIN selected_rows sr ON c.row_num > sr.row_num
    -- 当前行的in_id和out_id都未被使用过
    WHERE c.in_id <> ALL(sr.used_ids)
      AND c.out_id <> ALL(sr.used_ids)
    -- 确保每次选取符合条件的最小row_num，保证顺序性
    AND c.row_num = (
        SELECT MIN(row_num)
        FROM candidates
        WHERE row_num > sr.row_num
          AND in_id <> ALL(sr.used_ids)
          AND out_id <> ALL(sr.used_ids)
    )
)
SELECT row_num, in_id, out_id
FROM selected_rows
ORDER BY row_num;

逻辑说明 #

1. 锚点成员：首先选中row_num最小的记录（即row_num=1），将该行的in_id和out_id存入数组used_ids，作为已使用ID的初始集合。
2. 递归成员：每次从row_num大于当前选中行的记录中，筛选出in_id和out_id均不在used_ids数组中的行，且只选取其中row_num最小的记录（保证按顺序筛选），同时将该行的ID加入used_ids数组，更新已使用ID集合。
3. 最终结果：从递归CTE中提取所需列并按row_num排序，得到符合要求的结果集。

原查询的问题分析 #

你之前的查询用NOT EXISTS排除所有历史行中出现过相同ID的记录，但这不符合需求逻辑：比如row_num=5的in_id=208虽然在row_num=2出现过，但row_num=2并未被选中，所以208属于未使用的ID，应该被保留。原查询错误地将所有历史行都纳入判断范围，而非仅判断已选中的行。

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