用JOIN或递归CTE替代UNION ALL实现周度薪资关联查询

问题描述 #

现有员工表Table A与包含14周数据的周薪资表Table B，需要生成Table C，展示员工当周（This Week）与下周（Next Week）的薪资信息。当前通过多次UNION ALL实现，希望改用JOIN或递归CTE优化方案。

表结构 #

Table A（员工表） #

Table B（周薪资表） #

期望输出Table C #

当前尝试代码 #

Select A.*, B.* from Table A
Left Outer join Table B      on A.Id = B.ID and B.week = 'Week 1'
Left Outer join Table B AS C on A.Id = C.ID and C.week = 'Week 2'
UNION ALL
Select * from Table A
Left Outer join Table B on A.Id = B.ID and week = 'Week 2'
Left Outer join Table B AS C on A.Id = C.ID and C.week = 'Week 3'

解决方案 #

方案1：自JOIN结合周数字转换 #

先将Table B中的周文本转换为数字，方便关联下一周，再通过自连接关联当周与下周数据，最后与员工表关联确保所有员工都被覆盖：

WITH B_processed AS (
    -- 提取周数字，统一周文本格式
    SELECT 
        ID,
        Week,
        Salary,
        CAST(REGEXP_REPLACE(Week, '[^0-9]', '') AS INT) AS week_num
    FROM Table B
),
all_weeks AS (
    -- 获取所有存在的周数，以及对应的下一周
    SELECT 
        week_num,
        week_num + 1 AS next_week_num,
        CONCAT('week ', week_num) AS current_week_text,
        CONCAT('week ', week_num + 1) AS next_week_text
    FROM (SELECT DISTINCT week_num FROM B_processed) AS weeks
    -- 额外加入最后一周的下一周（如week3的下一周week4）
    UNION ALL
    SELECT MAX(week_num) + 1, NULL, CONCAT('week ', MAX(week_num) + 1), NULL
    FROM B_processed
),
employee_week_combinations AS (
    -- 生成所有员工与所有周的组合
    SELECT 
        A.ID,
        A.Employee_Name,
        aw.current_week_text,
        aw.next_week_text,
        aw.week_num,
        aw.next_week_num
    FROM Table A AS A
    CROSS JOIN all_weeks AS aw
)
SELECT 
    ewc.ID,
    bp_current.Week AS `This Week`,
    bp_current.Salary AS `This week Salary`,
    bp_next.Week AS `Next Week`,
    bp_next.Salary AS `Next week Salary`
FROM employee_week_combinations AS ewc
LEFT JOIN B_processed AS bp_current 
    ON ewc.ID = bp_current.ID AND ewc.week_num = bp_current.week_num
LEFT JOIN B_processed AS bp_next 
    ON ewc.ID = bp_next.ID AND ewc.next_week_num = bp_next.week_num
-- 过滤掉当周和下周都无薪资的无效行
WHERE bp_current.ID IS NOT NULL OR bp_next.ID IS NOT NULL
ORDER BY ewc.week_num, ewc.ID;

方案2：递归CTE生成周序列（适用于固定14周场景） #

如果已知是固定14周，可用递归CTE生成完整的周序列，再关联薪资数据：

WITH recursive_week AS (
    -- 递归生成1到14周的序列
    SELECT 1 AS week_num, 'week 1' AS week_text
    UNION ALL
    SELECT week_num + 1, CONCAT('week ', week_num + 1)
    FROM recursive_week
    WHERE week_num < 14
),
employee_week_combinations AS (
    -- 所有员工与所有周的组合
    SELECT 
        A.ID,
        A.Employee_Name,
        rw.week_num,
        rw.week_text AS current_week_text,
        CONCAT('week ', rw.week_num + 1) AS next_week_text,
        rw.week_num + 1 AS next_week_num
    FROM Table A AS A
    CROSS JOIN recursive_week AS rw
)
SELECT 
    ewc.ID,
    bp_current.Week AS `This Week`,
    bp_current.Salary AS `This week Salary`,
    bp_next.Week AS `Next Week`,
    bp_next.Salary AS `Next week Salary`
FROM employee_week_combinations AS ewc
LEFT JOIN Table B AS bp_current 
    ON ewc.ID = bp_current.ID AND ewc.current_week_text = bp_current.Week
LEFT JOIN Table B AS bp_next 
    ON ewc.ID = bp_next.ID AND ewc.next_week_text = bp_next.Week
WHERE bp_current.ID IS NOT NULL OR bp_next.ID IS NOT NULL
ORDER BY ewc.week_num, ewc.ID;

说明 #

两种方案都替代了繁琐的UNION ALL写法，扩展性更强：

• 方案1会自动识别Table B中已有的周数，适合周数不固定的场景；
• 方案2针对已知14周的固定场景，递归生成周序列更高效。

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