如何在WITH...AS块中引入变量并整合员工职能查询至维度源查询

首先得指出你原来写法里的一个小问题：你用全局变量@mainFunctionId只会获取第一个员工的主职能ID，没法正确区分每个员工自己的主次职能。咱们可以用CTE（WITH...AS）结合窗口函数来重构逻辑，这样既能把职能查询和维度源查询整合起来，又能准确给每个员工的职能标记主次。

完整解决方案代码 #

WITH EmployeeFunctions AS (
    -- 第一步：获取每个员工的职能信息及排序
    SELECT 
        e.EmployeeId,
        es.FunctionId,
        ef.Label,
        ISNULL(es.SortOrder, 9999) AS SortOrder -- 无排序值的职能默认放到最后
    FROM 
        Employee e
    INNER JOIN 
        Employee_Scope es ON es.EmployeeId = e.EmployeeId
    INNER JOIN 
        Employee_Function ef ON es.FunctionId = ef.FunctionId
),
EmployeeFunctionWithRole AS (
    -- 第二步：给每个员工的职能标记主次
    SELECT 
        EmployeeId,
        FunctionId,
        Label,
        CASE 
            WHEN ROW_NUMBER() OVER(PARTITION BY EmployeeId ORDER BY SortOrder ASC) = 1 THEN 'main'
            ELSE 'secondary'
        END AS Role
    FROM 
        EmployeeFunctions
)
-- 第三步：关联维度源查询和职能数据
SELECT 
    E.EmployeeId,
    E.AdminFileId,
    E.Lastname + ' ' + E.Firstname AS Name,
    ES.Status,
    EFR.Label,
    EFR.Role
FROM 
    Employee E
INNER JOIN 
    EmployeeStatus ES ON ES.EmployeeStatusId = E.EmployeeStatusId
INNER JOIN 
    EmployeeFunctionWithRole EFR ON EFR.EmployeeId = E.EmployeeId
ORDER BY 
    E.EmployeeId, EFR.SortOrder; -- 按员工ID和职能排序输出

关键逻辑说明 #

1. EmployeeFunctions CTE：把你原来嵌套的职能查询拆成独立的CTE，清晰获取每个员工的所有职能、对应标签，以及排序值（用ISNULL把无排序值的职能默认设为9999，确保它们排在次要职能队列）。
2. EmployeeFunctionWithRole CTE：用ROW_NUMBER() OVER(PARTITION BY EmployeeId ORDER BY SortOrder ASC)给每个员工的职能按排序编号——编号为1的就是该员工的主职能，其他为次要职能，通过CASE语句生成main/secondary标记。
3. 最终关联查询：把你的维度源查询（员工基本信息、状态）和带主次标记的职能数据关联，得到完整的目标输出。

示例输出（以EmployeeId=1为例） #

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