基于组织架构树标记会议Boss级参与者（SQL Server实现）

任务说明 #

基于给定的组织架构树，标记会议数据中的“Boss级”参与者，核心是找出每场会议中各汇报分支里的最高层级参会人员。

识别规则 #

• 识别会议中各汇报线（组织树分支/子树）内的最高层级人员为Boss；
• 若参与者的汇报子树无上级参会，则该参与者为Boss；
• 若其汇报子树有上级参会，则该参与者不是Boss；
• 会议可存在多个Boss（当他们不在组织树同一分支时）；
• 离根节点最近的参会员工为Boss，若其上级参会则上级不会成为Boss。

数据源 #

组织架构树数据 #

会议数据及预期结果 #

测试SQL脚本 #

-- 组织架构树测试数据
SELECT *
FROM (
    VALUES
        ('Root', NULL),
        ('Billy', 'Root'),
        ('Jacob', 'Billy'),
        ('Susan', 'Billy'),
        ('Tracy', 'Billy'),
        ('Sheri', 'Billy'),
        ('Carlos', 'Tracy'),
        ('Andrew', 'Tracy'),
        ('Hunter', 'Sheri'),
        ('Charly', 'Root'),
        ('Larry', 'Charly'),
        ('Harry', 'Charly'),
        ('Toni', 'Charly'),
        ('Chris', 'Larry'),
        ('Michael', 'Harry'),
        ('Anita', 'Toni')
) A (Name, Superior);

-- 会议数据及预期结果测试数据
SELECT *
FROM (
    VALUES
        ('Meeting 1', 'Tracy', 'Y'),
        ('Meeting 1', 'Hunter', 'Y'),
        ('Meeting 2', 'Billy', 'Y'),
        ('Meeting 2', 'Charly', 'Y'),
        ('Meeting 2', 'Hunter', 'N'),
        ('Meeting 2', 'Larry', 'N'),
        ('Meeting 3', 'Billy', 'Y'),
        ('Meeting 3', 'Hunter', 'N'),
        ('Meeting 3', 'Anita', 'Y'),
        ('Meeting 4', 'Anita', 'Y'),
        ('Meeting 4', 'Billy', 'Y'),
        ('Meeting 5', 'Anita', 'Y'),
        ('Meeting 6', 'Billy', 'Y'),
        ('Meeting 6', 'Charly', 'Y')
) A ([Meeting], [Name], [Meeting Boss (expected results)]);

需求 #

已猜测可使用递归CTE实现，但希望得到更简便的SQL Server（2019及以上）实现方式，同时想了解该问题的通用术语。

解决方案 #

简便实现方式（递归CTE+EXISTS） #

核心思路是先为每个参会人员递归遍历所有上级，再检查同一场会议中是否存在该人员的上级：若不存在，则标记为Boss。

WITH EmployeeHierarchy AS (
    -- 基础项：员工自身
    SELECT 
        Name,
        Superior
    FROM A -- 替换为你的组织架构表名
    UNION ALL
    -- 递归项：向上遍历所有上级
    SELECT 
        eh.Name,
        emp.Superior
    FROM EmployeeHierarchy eh
    JOIN A emp ON eh.Superior = emp.Name
)
SELECT 
    m.[Meeting],
    m.[Name],
    CASE 
        WHEN NOT EXISTS (
            SELECT 1 
            FROM EmployeeHierarchy eh
            WHERE eh.Name = m.[Name]
              AND eh.Superior IN (SELECT [Name] FROM Meetings WHERE [Meeting] = m.[Meeting])
        ) THEN 'Y'
        ELSE 'N'
    END AS [Meeting Boss]
FROM Meetings m -- 替换为你的会议数据表名
ORDER BY m.[Meeting], m.[Name];

高效优化版（SQL Server 2019+特性） #

利用STRING_AGG生成完整汇报链，再通过STRING_SPLIT拆分上级列表，判断是否有上级参会：

WITH RecursiveHierarchy AS (
    SELECT 
        Name,
        Superior,
        1 AS Level
    FROM A
    UNION ALL
    SELECT 
        rh.Name,
        emp.Superior,
        rh.Level + 1
    FROM RecursiveHierarchy rh
    JOIN A emp ON rh.Superior = emp.Name
),
EmployeeSuperiors AS (
    SELECT 
        Name,
        STRING_AGG(Superior, '|') WITHIN GROUP (ORDER BY Level DESC) AS AllSuperiors
    FROM RecursiveHierarchy
    WHERE Superior IS NOT NULL
    GROUP BY Name
)
SELECT 
    m.[Meeting],
    m.[Name],
    CASE 
        WHEN NOT EXISTS (
            SELECT 1 
            FROM STRING_SPLIT(ISNULL(es.AllSuperiors, ''), '|') s
            JOIN Meetings m2 ON m2.[Name] = s.value AND m2.[Meeting] = m.[Meeting]
        ) THEN 'Y'
        ELSE 'N'
    END AS [Meeting Boss]
FROM Meetings m
LEFT JOIN EmployeeSuperiors es ON m.[Name] = es.Name
ORDER BY m.[Meeting], m.[Name];

通用术语 #

这个问题属于层级数据处理（Hierarchical Data Processing）范畴，核心需求是找出会议中的最高层级参会者，也可称为识别组织分支中的顶级参会节点（Top-level Attendee Nodes in Organizational Branches）。这类场景常见于企业协作系统的会议分析、权限校验等业务中。

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