求助：基于递归CTE实现候选者与唯一职位匹配的SQL查询

你需要为每个候选人分配全表唯一的职位，规则是选取每个候选人第一个未被之前候选人占用的JobId，递归CTE正好适合这种按顺序处理、依赖前序结果的场景，以下是具体实现方案：

假设你的原CTE定义如下（替换为你实际的CTE） #

WITH CandidateApplications AS (
    SELECT 1 AS ApplicationID, 1 AS CandidateId, 1 AS JobId, 1 AS Row UNION ALL
    SELECT 2, 1, 2, 2 UNION ALL
    SELECT 3, 1, 3, 3 UNION ALL
    SELECT 4, 2, 1, 1 UNION ALL
    SELECT 5, 2, 2, 2 UNION ALL
    SELECT 6, 2, 5, 3 UNION ALL
    SELECT 7, 3, 2, 1 UNION ALL
    SELECT 8, 3, 6, 2 UNION ALL
    SELECT 9, 3, 3, 3
)

递归CTE实现代码 #

WITH CandidateApplications AS (
    -- 替换成你实际的CTE定义
    SELECT 1 AS ApplicationID, 1 AS CandidateId, 1 AS JobId, 1 AS Row UNION ALL
    SELECT 2, 1, 2, 2 UNION ALL
    SELECT 3, 1, 3, 3 UNION ALL
    SELECT 4, 2, 1, 1 UNION ALL
    SELECT 5, 2, 2, 2 UNION ALL
    SELECT 6, 2, 5, 3 UNION ALL
    SELECT 7, 3, 2, 1 UNION ALL
    SELECT 8, 3, 6, 2 UNION ALL
    SELECT 9, 3, 3, 3
),
RecursiveAssignments AS (
    -- 锚点成员：处理第一个候选人，取其第一条申请，初始化已占用职位集合
    SELECT 
        ca.ApplicationID,
        ca.CandidateId,
        ca.JobId,
        ca.Row,
        CAST(ca.JobId AS VARCHAR(MAX)) AS OccupiedJobs
    FROM CandidateApplications ca
    WHERE ca.CandidateId = (SELECT MIN(CandidateId) FROM CandidateApplications)
    AND ca.Row = 1

    UNION ALL

    -- 递归成员：依次处理后续候选人，找到第一个未被占用的职位
    SELECT 
        ca.ApplicationID,
        ca.CandidateId,
        ca.JobId,
        ca.Row,
        ra.OccupiedJobs + ',' + CAST(ca.JobId AS VARCHAR(MAX)) AS OccupiedJobs
    FROM RecursiveAssignments ra
    -- 获取下一个未处理的候选人ID
    JOIN (
        SELECT MIN(CandidateId) AS NextCandidateId
        FROM CandidateApplications
        WHERE CandidateId > ra.CandidateId
    ) nc ON 1=1
    -- 匹配该候选人的申请，筛选未被占用的职位
    JOIN CandidateApplications ca 
        ON ca.CandidateId = nc.NextCandidateId
        AND CHARINDEX(',' + CAST(ca.JobId AS VARCHAR(MAX)) + ',', ',' + ra.OccupiedJobs + ',') = 0
    -- 确保取的是该候选人第一个未被占用的职位（Row最小的那条）
    WHERE NOT EXISTS (
        SELECT 1
        FROM CandidateApplications ca2
        WHERE ca2.CandidateId = ca.CandidateId
        AND ca2.Row < ca.Row
        AND CHARINDEX(',' + CAST(ca2.JobId AS VARCHAR(MAX)) + ',', ',' + ra.OccupiedJobs + ',') = 0
    )
)
-- 输出最终分配结果
SELECT ApplicationID, CandidateId, JobId, Row
FROM RecursiveAssignments;

核心逻辑解释 #

1. 锚点成员：作为递归的起点，先处理ID最小的候选人，取他的第一条申请（Row=1），同时将该JobId存入已占用集合，作为后续判断的依据。
2. 递归成员：每次从已处理的结果出发，找到下一个未处理的候选人，遍历他的申请记录（按Row从小到大），筛选出第一个不在已占用集合里的职位，将这条记录加入结果集，并更新已占用职位集合。
3. 递归会自动终止于所有候选人都被处理完毕的时刻。

适配不同SQL方言的注意点 #

• 如果使用PostgreSQL，可以用数组类型存储已占用职位，替换字符串拼接逻辑，查询时用@>操作符判断是否存在，效率更高。
• 如果使用SQL Server，也可以用STRING_AGG或JSON数组来管理已占用职位集合。
• 确保原CTE中每个候选人的申请记录是按Row字段从小到大排序的，这样才能保证取到“第一个”符合条件的职位。

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