如何用T-SQL生成tblTimetable中PersonID-TimetableID对的全组合

场景与需求 #

车间内3名及以上员工使用不同时间表协同工作，需生成所有包含全部员工ID的(PersonID, TimetableID)组合——每个组合中每个员工对应自身的一个时间表ID，最终结果将作为Access报表的记录源，每个组合对应报表的一页。

表结构与数据 #

tblTimetable表（员工-时间表映射） #

CREATE TABLE tblTimetable
(
    PersonID INT,
    TimetableID INT
);

INSERT INTO tblTimetable (PersonID, TimetableID)
VALUES (5215, 57), (18943, 221), (18943, 230), (18943, 238),
       (21488, 257), (21488, 270), (5215, 67), (5215, 77),
       (5215, 87), (5215, 97);

tblTimetableDetail表（时间表详情，暂不处理） #

CREATE TABLE tblTimetableDetail
(
    TimetableID INT,
    WeekdayID INT,
    BeginTime DATETIME,
    EndTime DATETIME
);

现有尝试与不足 #

当前使用的SQL仅能获取每个员工的最小/最大时间表ID组合，无法生成全量组合：

SELECT PersonID, MIN(TimetableID) 
FROM tblTimetable
GROUP BY PersonID

UNION ALL

SELECT PersonID, MAX(TimetableID) 
FROM tblTimetable
GROUP BY PersonID

仅得到2组结果，远少于实际需要的5*3*2=30组全组合。

期望结果 #

需要生成包含所有可能组合的列表，格式如下：

CombiNo PersonID TimetableID
1       5215       57
1       18943      221
1       21488      257

2       5215       57
2       18943      221
2       21488      270

...

30      5215       97
30      18943      238
30      21488      270

T-SQL解决方案 #

通过笛卡尔积生成全组合+列转行实现需求，具体SQL如下：

WITH cte5215 AS (
    -- 为员工5215的时间表生成行号
    SELECT 
        TimetableID,
        ROW_NUMBER() OVER (ORDER BY TimetableID) AS RN5215
    FROM tblTimetable 
    WHERE PersonID = 5215
),
cte18943 AS (
    -- 为员工18943的时间表生成行号
    SELECT 
        TimetableID,
        ROW_NUMBER() OVER (ORDER BY TimetableID) AS RN18943
    FROM tblTimetable 
    WHERE PersonID = 18943
),
cte21488 AS (
    -- 为员工21488的时间表生成行号
    SELECT 
        TimetableID,
        ROW_NUMBER() OVER (ORDER BY TimetableID) AS RN21488
    FROM tblTimetable 
    WHERE PersonID = 21488
),
cteFullCombinations AS (
    -- 生成所有组合的笛卡尔积，并计算组合编号
    SELECT 
        -- 组合编号计算公式：(员工1行号-1)*员工2时间表数*员工3时间表数 + (员工2行号-1)*员工3时间表数 + 员工3行号
        (RN5215 - 1) * 3 * 2 + (RN18943 - 1) * 2 + RN21488 AS CombiNo,
        5215 AS PersonID_5215, TimetableID AS TT_5215,
        18943 AS PersonID_18943, TimetableID AS TT_18943,
        21488 AS PersonID_21488, TimetableID AS TT_21488
    FROM cte5215
    CROSS JOIN cte18943
    CROSS JOIN cte21488
)
-- 将列格式的组合转为行格式
SELECT 
    CombiNo,
    PersonID,
    TimetableID
FROM cteFullCombinations
UNPIVOT (
    TimetableID FOR PersonID IN 
        ([PersonID_5215], [PersonID_18943], [PersonID_21488])
) AS UnpivotResult
ORDER BY CombiNo, PersonID;

方案说明 #

1. CTE生成行号：为每个员工的时间表记录生成唯一行号，用于后续组合编号计算。
2. 笛卡尔积生成全组合：通过CROSS JOIN获取所有员工时间表的笛卡尔积，得到所有可能的组合。
3. 计算组合编号：基于各员工的时间表数量，通过行号计算唯一的组合编号，确保每个组合对应唯一编号。
4. 列转行：使用UNPIVOT将多列的组合数据转换为期望的行格式，符合报表需求。

扩展说明 #

若后续员工数量增加，只需：

• 添加对应员工的CTE（生成行号）
• 修改cteFullCombinations中的组合编号计算公式（乘以新增员工的时间表数量）
• 更新UNPIVOT中的列列表

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