如何在SQL中为同一业务-编码组合分配多个角色值？

现有dbo.business表，结构如下：

CREATE TABLE dbo.business
(
    Business varchar(50),
    Code     varchar(10),
    Role     varchar(50)
)

已插入数据：

INSERT INTO dbo.business
    SELECT 'Sales', '9000', NULL
    UNION
    SELECT 'Sales', '9000', NULL
    UNION
    SELECT 'Mortgage', '5000', NULL
    UNION
    SELECT 'Mortgage', '5200', NULL
    UNION
    SELECT 'Sales', '9100', NULL

需根据Business与Code的组合为Role字段分配对应角色（例如Mortgage+5000需分配3个角色），期望输出如下：

Business    Code   Role
----------------------------------
Mortgage    5000   R_Specialist
Mortgage    5000   ProjectManager
Mortgage    5000   RuralManager
Mortgage    5200   C_Specialist
Mortgage    5200   ProjectManager
Mortgage    5200   RuralManager
Sales       9000   TechnicalManager
Sales       9100   Specialist

原方案通过多个UNION拼接查询实现，但业务和编码组合较多时语句冗余，需更优实现方式。

推荐使用角色映射表+关联查询的方式，替代冗余的UNION拼接，提升可维护性和可读性。

1. 创建角色映射表（长期维护场景） #

先创建一个存储Business+Code与对应角色映射关系的表，后续新增/修改角色只需维护此表：

CREATE TABLE dbo.BusinessRoleMapping
(
    Business varchar(50),
    Code     varchar(10),
    Role     varchar(50)
)

插入映射数据：

INSERT INTO dbo.BusinessRoleMapping
SELECT 'Mortgage', '5000', 'R_Specialist' UNION ALL
SELECT 'Mortgage', '5000', 'ProjectManager' UNION ALL
SELECT 'Mortgage', '5000', 'RuralManager' UNION ALL
SELECT 'Mortgage', '5200', 'C_Specialist' UNION ALL
SELECT 'Mortgage', '5200', 'ProjectManager' UNION ALL
SELECT 'Mortgage', '5200', 'RuralManager' UNION ALL
SELECT 'Sales', '9000', 'TechnicalManager' UNION ALL
SELECT 'Sales', '9100', 'Specialist'

2. 关联查询生成结果 #

通过INNER JOIN或CROSS APPLY关联原表与映射表，用DISTINCT去除原表中重复的Business+Code组合带来的重复角色行：

方式一：INNER JOIN #

SELECT DISTINCT
    b.Business,
    b.Code,
    brm.Role
FROM dbo.business b
INNER JOIN dbo.BusinessRoleMapping brm
    ON b.Business = brm.Business AND b.Code = brm.Code
ORDER BY b.Business, b.Code, brm.Role

方式二：CROSS APPLY #

SELECT DISTINCT 
    b.Business, 
    b.Code, 
    brm.Role
FROM dbo.business b
CROSS APPLY (
    SELECT Role 
    FROM dbo.BusinessRoleMapping 
    WHERE Business = b.Business AND Code = b.Code
) brm
ORDER BY b.Business, b.Code, brm.Role

3. 临时映射场景（无需物理表） #

如果映射关系无需长期维护，可使用CTE（公共表表达式）临时存储映射数据：

WITH BusinessRoleMapping AS (
    SELECT 'Mortgage' AS Business, '5000' AS Code, 'R_Specialist' AS Role UNION ALL
    SELECT 'Mortgage', '5000', 'ProjectManager' UNION ALL
    SELECT 'Mortgage', '5000', 'RuralManager' UNION ALL
    SELECT 'Mortgage', '5200', 'C_Specialist' UNION ALL
    SELECT 'Mortgage', '5200', 'ProjectManager' UNION ALL
    SELECT 'Mortgage', '5200', 'RuralManager' UNION ALL
    SELECT 'Sales', '9000', 'TechnicalManager' UNION ALL
    SELECT 'Sales', '9100', 'Specialist'
)
SELECT DISTINCT
    b.Business,
    b.Code,
    brm.Role
FROM dbo.business b
INNER JOIN BusinessRoleMapping brm
    ON b.Business = brm.Business AND b.Code = brm.Code
ORDER BY b.Business, b.Code, brm.Role

方案优势 #

• 可维护性：新增/修改角色或业务编码组合时，仅需更新映射表（或CTE），无需修改核心查询语句，避免大量UNION的冗余。
• 可读性：查询逻辑清晰，比一堆UNION拼接更容易理解和调试。
• 扩展性：后续新增更多业务规则时，直接在映射表中添加数据即可，无需改动查询结构。

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