T-SQL实现多数据项排列组合：员工多维度信息枚举需求

这个需求其实用CTE（公共表表达式）结合UNION ALL和笛卡尔积就能轻松搞定，我给你一步步拆解实现步骤：

1. 先明确表结构（可根据实际表名调整） #

• 基础表：Employee，包含员工核心信息，比如EmployeeID, Name, BaseDepartment, BaseRole, BaseLocation
• 额外部门表：ExtraDepartments，字段EmployeeID, ExtraDepartment
• 额外角色表：ExtraRoles，字段EmployeeID, ExtraRole
• 额外地点表：ExtraLocations，字段EmployeeID, ExtraLocation

2. 编写核心SQL代码 #

我们先通过CTE分别把每个员工的「基础值+对应补充值」合并，再对这三个合并后的集合做笛卡尔积，就能得到所有排列组合：

WITH EmployeeAllDepartments AS (
    -- 合并基础部门和额外部门
    SELECT EmployeeID, BaseDepartment AS Department
    FROM Employee
    UNION ALL
    SELECT EmployeeID, ExtraDepartment AS Department
    FROM ExtraDepartments
),
EmployeeAllRoles AS (
    -- 合并基础角色和额外角色
    SELECT EmployeeID, BaseRole AS Role
    FROM Employee
    UNION ALL
    SELECT EmployeeID, ExtraRole AS Role
    FROM ExtraRoles
),
EmployeeAllLocations AS (
    -- 合并基础地点和额外地点
    SELECT EmployeeID, BaseLocation AS Location
    FROM Employee
    UNION ALL
    SELECT EmployeeID, ExtraLocation AS Location
    FROM ExtraLocations
)
-- 生成全排列组合
SELECT 
    e.EmployeeID,
    e.Name,
    ed.Department,
    er.Role,
    el.Location
FROM Employee e
INNER JOIN EmployeeAllDepartments ed 
    ON e.EmployeeID = ed.EmployeeID
INNER JOIN EmployeeAllRoles er 
    ON e.EmployeeID = er.EmployeeID
INNER JOIN EmployeeAllLocations el 
    ON e.EmployeeID = el.EmployeeID
-- 可选：如果需要去重（比如补充表有重复记录），加上下面一行
-- GROUP BY e.EmployeeID, e.Name, ed.Department, er.Role, el.Location

3. 代码逻辑解释 #

• 每个CTE的作用是把员工的基础信息和对应补充表的信息合并：比如EmployeeAllDepartments会包含员工的基础部门，以及所有额外部门的记录。
• 用INNER JOIN关联三个CTE和基础表，本质是对每个员工的「所有部门选项」「所有角色选项」「所有地点选项」做笛卡尔积，自然生成所有可能的排列组合。
• 举个例子：如果某员工基础地点是纽约，额外地点有伦敦，那EmployeeAllLocations会有两行记录；如果同时有额外角色“秘书”，那EmployeeAllRoles会有基础角色+秘书两行；最终会生成2（地点）×2（角色）=4行数据，完全符合你要的效果。

4. 注意事项 #

• 如果补充表中存在重复的记录（比如同一个员工重复添加了同一个额外部门），可以加上GROUP BY或者把UNION ALL改成UNION来自动去重。
• 如果某个员工没有任何补充信息，三个CTE都只会保留基础值的一行，最终结果就是一行基础数据，符合预期。

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