如何用SQL视图实现Type 2 SCD的当前职位关联查询？

问题1：为Type 2 SCD员工维度表添加当前职位ID #

原始表结构 #

目标结果 #

SQL实现（视图形式） #

可以通过递归CTE追踪员工的职位变更链，找到最终的当前职位，无需依赖job_id的递增顺序：

CREATE VIEW emp_current_job_view AS
WITH RECURSIVE emp_job_hierarchy AS (
    -- 初始化所有员工职位记录
    SELECT
        Empl,
        job_id,
        job_id AS candidate_current,
        old_job_id
    FROM your_employee_table
    UNION ALL
    -- 递归遍历职位变更链，更新当前职位候选值
    SELECT
        ejh.Empl,
        ejh.job_id,
        et.job_id,
        et.old_job_id
    FROM emp_job_hierarchy ejh
    JOIN your_employee_table et 
        ON ejh.Empl = et.Empl 
        AND ejh.candidate_current = et.old_job_id
)
-- 筛选出每个职位对应的最终当前职位（无后续变更的节点）
SELECT
    Empl,
    job_id,
    candidate_current AS current_job_id
FROM emp_job_hierarchy ejh
WHERE NOT EXISTS (
    SELECT 1 
    FROM your_employee_table et 
    WHERE et.Empl = ejh.Empl 
    AND et.old_job_id = ejh.candidate_current
);

如果可以确认job_id是按职位变更时间递增的，也可以用更简洁的窗口函数方案：

CREATE VIEW emp_current_job_view AS
SELECT
    Empl,
    job_id,
    MAX(job_id) OVER (PARTITION BY Empl) AS current_job_id
FROM your_employee_table;

问题2：职位表映射到当前生效职位及描述 #

原始表结构 #

目标结果 #

SQL实现（视图形式） #

使用递归CTE遍历职位的替换链，找到每个职位对应的最终生效职位：

CREATE VIEW job_current_info_view AS
WITH RECURSIVE job_hierarchy AS (
    -- 初始化所有职位记录
    SELECT
        job_id,
        job_id AS current_job_id,
        job_desc AS current_job_desc
    FROM your_job_table
    UNION ALL
    -- 递归追踪职位替换关系，更新当前职位信息
    SELECT
        jh.job_id,
        j.job_id,
        j.job_desc
    FROM job_hierarchy jh
    JOIN your_job_table j 
        ON jh.current_job_id = j.old_job_id
)
-- 筛选出每个职位的最终当前职位（无后续替换的节点）
SELECT
    job_id,
    current_job_id,
    current_job_desc
FROM job_hierarchy jh
WHERE NOT EXISTS (
    SELECT 1 
    FROM your_job_table j 
    WHERE j.old_job_id = jh.current_job_id
)
ORDER BY job_id;

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