TSQL 基于RDWR实现候选人可投递岗位查询无结果故障排查

• 有两张映射表：CandidatesSkills存储候选人与其所拥有技能的对应关系，JobRequirements存储岗位与该岗位所需技能的映射关系
• 投递规则：候选人拥有某岗位的全部要求技能即可投递该岗位，允许候选人拥有额外技能，给定CandidateID需要查询该候选人可投递的全部岗位
• 示例数据集下的预期返回结果：JobID为2、3、5
• 原编写查询SQL如下：

DECLARE @CandidateID INT = 1

SELECT JobID 
FROM        
(
    SELECT jr.JobID
        ,cnt=SUM(CASE WHEN jr.SkillID = c.SkillID THEN 1 ELSE 0 END)
        ,Items=COUNT(*)
    FROM dbo.JobRequirements AS jr
    CROSS JOIN dbo.CandidatesSkills  AS c    
    WHERE c.CandidateID = @CandidateID
    GROUP BY jr.JobID, jr.SkillID
) d
GROUP BY JobID
HAVING  SUM(cnt) = MIN(Items)
    AND MIN(cnt) >= 0;    

原SQL无结果返回的核心错误有两点：

1. 内层分组维度错误：内层按jr.JobID、jr.SkillID分组，此时计算得到的Items=COUNT(*)是单个岗位单条技能要求对应的匹配次数，并非该岗位要求的总技能数，导致后续HAVING的判断逻辑完全不成立。
2. 无效笛卡尔积：直接使用CROSS JOIN关联两张表会产生大量冗余数据，性能损耗严重。

简洁实现版本 #

采用左联分组统计逻辑即可高效实现需求：

DECLARE @CandidateID INT = 1

SELECT jr.JobID
FROM JobRequirements jr
LEFT JOIN CandidatesSkills c 
    ON c.SkillID = jr.SkillID 
    AND c.CandidateID = @CandidateID
GROUP BY jr.JobID
-- 岗位要求的技能总数与候选人匹配到的技能数相等，即满足全部要求
HAVING COUNT(jr.SkillID) = COUNT(c.SkillID)

兼容RDWR风格版本 #

如果需要保留原参考的关系除写法风格，可调整分组统计逻辑：

DECLARE @CandidateID INT = 1

SELECT JobID 
FROM        
(
    SELECT 
        jr.JobID,
        -- 窗口函数统计当前岗位要求的总技能数
        total_required = COUNT(jr.SkillID) OVER(PARTITION BY jr.JobID),
        -- 标记当前技能候选人是否拥有
        is_match = CASE WHEN c.SkillID IS NOT NULL THEN 1 ELSE 0 END
    FROM dbo.JobRequirements AS jr
    LEFT JOIN dbo.CandidatesSkills AS c    
        ON c.SkillID = jr.SkillID AND c.CandidateID = @CandidateID
) d
GROUP BY JobID, total_required
HAVING SUM(is_match) = total_required

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