基于条件动态选择列的SQL实现求助（含表结构与预期输出）

表结构说明 #

现有两张表：

• Table1：包含EmployeeId（员工ID）、CompanyId（公司ID）、StartDate（任职开始日期）、EndDate（任职结束日期）字段；
• Table2：包含EmployeeId（员工ID）、CompanyId（公司ID）、AllMonths（全月统一分类）、Month（月份）、Value（分类值）字段。

表创建与数据插入脚本 #

CREATE TABLE [dbo].[Table1](
    [EmployeeId] [int] NULL,
    [CompanyId] [int] NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Table1] VALUES(12345,1205,'2021-01-01 00:00:00.000','2021-06-30 00:00:00.000')
INSERT INTO [dbo].[Table1] VALUES(23211,1205,'2021-01-01 00:00:00.000','2021-05-31 00:00:00.000')
INSERT INTO [dbo].[Table1] VALUES(23211,1205,'2021-07-01 00:00:00.000','2021-09-30 00:00:00.000')
INSERT INTO [dbo].[Table1] VALUES(23141,1205,'2021-01-01 00:00:00.000','2021-11-30 00:00:00.000')
INSERT INTO [dbo].[Table1] values(54333,1205,'2021-01-01 00:00:00.000','2021-05-31 00:00:00.000') 
INSERT INTO [dbo].[Table1] values(76553,1205,'2021-01-01 00:00:00.000','2021-12-31 00:00:00.000')
INSERT INTO [dbo].[Table1] values(55555,1205,'2021-08-01 00:00:00.000','2021-09-30 00:00:00.000')
INSERT INTO [dbo].[Table1] values(55555,1205,'2021-11-01 00:00:00.000','2021-11-30 00:00:00.000')


CREATE TABLE [dbo].[Table2](
    [EmployeeId] [int] NULL,
    [CompanyId] [int] NULL,
    [AllMonths] [int] NULL,
    [Month] [int] NULL,
    [Value] [int] NULL
)

INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,1,1)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,2,1)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,3,1)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,4,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,5,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,6,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,7,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,8,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,9,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,11,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,12,2)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,1,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,2,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,3,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,4,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,5,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,6,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,7,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,8,2)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,9,2)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,11,2)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,12,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,1,1) 
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,2,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,3,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,4,2) 
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,5,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,6,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,7,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,8,1)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,9,1)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,10,1)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,11,1)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,12,1)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,1,1)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,2,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,3,2) 
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,4,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,5,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,6,2) 
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,7,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,8,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,9,2) 
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,10,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,11,NULL)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,12,NULL)

预期输出规则 #

• 员工12345：AllMonths为NULL，任职区间内Table2的Value值一致，输出EmployeeClass为1；
• 员工23211：AllMonths为NULL，分两个任职区间，区间内Value有变化，需拆分多行输出对应EmployeeClass；
• 员工23141：AllMonths不为NULL，EmployeeClass取AllMonths的值1；
• 员工54333：AllMonths为NULL，任职区间内Value有变化，拆分多行输出对应EmployeeClass；
• 员工55555：Table1有数据但Table2无对应记录，输出EmployeeClass为NULL；
• 员工76553：AllMonths为NULL，任职区间内Value分三类，拆分多行输出对应EmployeeClass，无Value的区间输出NULL。

SQL解决方案 #

WITH EmployeeMonths AS (
    -- 生成每个员工任职区间内的所有月份区间
    SELECT 
        t1.EmployeeId,
        t1.CompanyId,
        t1.StartDate,
        t1.EndDate,
        DATEADD(MONTH, n-1, DATEFROMPARTS(YEAR(t1.StartDate), MONTH(t1.StartDate), 1)) AS MonthStart,
        DATEADD(DAY, -1, DATEADD(MONTH, n, DATEFROMPARTS(YEAR(t1.StartDate), MONTH(t1.StartDate), 1))) AS MonthEnd,
        MONTH(DATEADD(MONTH, n-1, t1.StartDate)) AS MonthNum
    FROM Table1 t1
    CROSS JOIN (SELECT number+1 AS n FROM master..spt_values WHERE type='P' AND number <= 11) nums
    WHERE DATEADD(MONTH, n-1, t1.StartDate) <= t1.EndDate
),
EmployeeValueGroups AS (
    -- 按员工、任职区间、Value分组，合并连续相同Value的月份
    SELECT 
        em.EmployeeId,
        em.CompanyId,
        em.StartDate AS EmploymentStart,
        em.EndDate AS EmploymentEnd,
        t2.Value,
        MIN(em.MonthStart) AS GroupStart,
        MAX(em.MonthEnd) AS GroupEnd
    FROM EmployeeMonths em
    LEFT JOIN Table2 t2 
        ON em.EmployeeId = t2.EmployeeId 
        AND em.CompanyId = t2.CompanyId 
        AND em.MonthNum = t2.Month
    GROUP BY em.EmployeeId, em.CompanyId, em.StartDate, em.EndDate, t2.Value,
        DATEADD(MONTH, 
            -DENSE_RANK() OVER (PARTITION BY em.EmployeeId, em.StartDate, em.EndDate ORDER BY em.MonthStart), 
            em.MonthStart)
),
AllMonthsClass AS (
    -- 提取AllMonths不为NULL的员工分类
    SELECT DISTINCT
        EmployeeId,
        CompanyId,
        StartDate,
        EndDate,
        AllMonths AS EmployeeClass
    FROM Table1 t1
    JOIN Table2 t2 ON t1.EmployeeId = t2.EmployeeId AND t1.CompanyId = t2.CompanyId
    WHERE t2.AllMonths IS NOT NULL
)
-- 合并所有场景的最终查询
SELECT 
    COALESCE(amc.EmployeeId, evg.EmployeeId, t1.EmployeeId) AS EmployeeId,
    COALESCE(amc.CompanyId, evg.CompanyId, t1.CompanyId) AS CompanyId,
    COALESCE(amc.StartDate, evg.GroupStart, t1.StartDate) AS StartDate,
    COALESCE(amc.EndDate, evg.GroupEnd, t1.EndDate) AS EndDate,
    COALESCE(amc.EmployeeClass, evg.Value) AS EmployeeClass
FROM Table1 t1
LEFT JOIN AllMonthsClass amc 
    ON t1.EmployeeId = amc.EmployeeId 
    AND t1.CompanyId = amc.CompanyId 
    AND t1.StartDate = amc.StartDate 
    AND t1.EndDate = amc.EndDate
LEFT JOIN EmployeeValueGroups evg 
    ON t1.EmployeeId = evg.EmployeeId 
    AND t1.CompanyId = evg.CompanyId 
    AND t1.StartDate = evg.EmploymentStart 
    AND t1.EndDate = evg.EmploymentEnd
ORDER BY EmployeeId, StartDate;

方案说明 #

1. EmployeeMonths：将员工的连续任职区间拆分为单个月份的区间，方便匹配Table2的月度数据；
2. EmployeeValueGroups：通过窗口函数将连续相同Value的月份合并为一个区间，处理Value变化时的拆分需求；
3. AllMonthsClass：单独处理AllMonths不为NULL的员工，直接取该字段值作为分类；
4. 最终通过LEFT JOIN合并所有场景，确保覆盖所有员工的情况，包括Table2无数据的员工。

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