如何验证候选人角色转移有效性：判断Table A步骤是否全存在于Table B

现有两张表：

• Table A：字段为 candidate_ref, role_num, step, action_date，记录候选人对应角色的流程步骤
• Table B：字段为 candidate_ref, role_num, new_role_num, step, action_date，记录候选人的角色转移及目标角色的流程步骤

以候选人231为例，其原角色为1（记录在Table A），角色转移信息在Table B中：该候选人从角色1转移至角色7、9、21，但仅转移至角色7为有效转移——因为角色1在Table A中的所有step，都能在Table B中角色7的记录里找到。

需求：找到判断有效角色转移的方法，最终在Table B中添加valid_move标记，有效转移（如new_role_num=7）标记为1，无效转移（如9、21）标记为0。此前尝试用full outer join未达到预期效果，以下是测试数据的SQL创建代码：

drop table if exists #table_a
create table #table_a
(
candidate_ref int
,role_num int
,step varchar(25)
,action_date datetime
)

drop table if exists #table_b
create table #table_b
(
candidate_ref int
,role_num int
,new_role_num int
,step varchar(25)
,action_date datetime
)

insert into #table_a
select 231, 1,  'New application', '2021-08-17' union
select 231, 1,  'On hold', '2022-02-02' union
select 231, 1,  'Pending reject','2022-02-28' union
select 231, 1,  'Rejection / Not suitable', '2022-02-28' union
select 231, 1,  'Online withdrawal',    '2022-10-24'

insert into #table_b
select 231,1,9,'Candidate withdrawal', '2021-08-07' union 
select 231,1,7,'New application', '2023-03-01' union 
select 231,1,7,'On hold', '2021-08-07' union 
select 231,1,7,'Pending reject', '2022-02-02' union 
select 231,1,7,'Rejection / Not suitable', '2022-02-28' union 
select 231,1,7,'Online withdrawal', '2022-02-28' union 
select 231,1,21,'New application', '2022-11-27' union 
select 231,1,21,'On hold', '2022-11-27' 

核心思路：先统计原角色（candidate_ref+role_num）的唯一step总数，再统计每个转移目标（candidate_ref+role_num+new_role_num）覆盖的原角色step数量，当覆盖数等于原总数时，判定为有效转移。

具体SQL实现如下：

-- 统计每个原角色的唯一step数量
WITH original_steps AS (
    SELECT 
        candidate_ref,
        role_num,
        COUNT(DISTINCT step) AS total_steps
    FROM #table_a
    GROUP BY candidate_ref, role_num
),
-- 统计每个转移目标覆盖的原角色step数量
matched_steps AS (
    SELECT 
        b.candidate_ref,
        b.role_num,
        b.new_role_num,
        COUNT(DISTINCT a.step) AS matched_count
    FROM #table_b b
    LEFT JOIN #table_a a 
        ON b.candidate_ref = a.candidate_ref
        AND b.role_num = a.role_num
        AND b.step = a.step
    GROUP BY b.candidate_ref, b.role_num, b.new_role_num
)
-- 关联回Table B，添加valid_move标记
SELECT 
    b.*,
    CASE 
        WHEN m.matched_count = o.total_steps THEN 1
        ELSE 0
    END AS valid_move
FROM #table_b b
JOIN original_steps o 
    ON b.candidate_ref = o.candidate_ref
    AND b.role_num = o.role_num
JOIN matched_steps m 
    ON b.candidate_ref = m.candidate_ref
    AND b.role_num = m.role_num
    AND b.new_role_num = m.new_role_num
ORDER BY b.new_role_num, b.step;

执行上述SQL后，Table B的每条记录会新增valid_move字段：

• 所有new_role_num=7的记录，valid_move为1（有效转移）
• new_role_num=9和21的记录，valid_move为0（无效转移）

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