AdventureWorks2016中用hierarchyid查询直属上级更年轻司龄更短的员工

原SQL核心错误点 #

• 上下级关联条件逻辑完全错误：原代码中OrganizationLevel判断为自引用无意义，上下级归属关系写反，导致子查询始终返回空值
• 采用多标量子查询取员工字段，若单个上级存在多名下属会直接触发多行返回报错
• 未添加需求要求的核心筛选条件：上级年龄更小、上级任职时间更短
• 仅关联了一次Person.Person表，无法同时获取上级和员工的完整姓名

修正后可运行SQL #

SELECT
    -- 上级相关字段
    CONCAT(p_manager.LastName, ' ', p_manager.FirstName) AS 'Manager Name',
    emp_manager.HireDate AS 'Date of hiring a manager',
    emp_manager.BirthDate AS 'Head\'s date of birth',
    -- 员工相关字段
    CONCAT(p_emp.LastName, ' ', p_emp.FirstName) AS 'Employee name',
    emp_emp.HireDate AS 'Employee hiring date',
    emp_emp.BirthDate AS 'Employee\'s date of birth'
FROM 
    HumanResources.Employee emp_emp
    -- 关联直属上级：GetAncestor(1) 直接取员工组织节点的直属父节点，对应直属上级
    JOIN HumanResources.Employee emp_manager 
        ON emp_emp.OrganizationNode.GetAncestor(1) = emp_manager.OrganizationNode
    -- 关联上级姓名表
    JOIN Person.Person p_manager 
        ON emp_manager.BusinessEntityID = p_manager.BusinessEntityID
    -- 关联员工姓名表
    JOIN Person.Person p_emp 
        ON emp_emp.BusinessEntityID = p_emp.BusinessEntityID
WHERE
    -- 上级生日更晚 = 年龄更小
    emp_manager.BirthDate > emp_emp.BirthDate
    -- 上级入职更晚 = 任职时间更短
    AND emp_manager.HireDate > emp_emp.HireDate
ORDER BY
    'Manager Name' ASC

逻辑说明 #

• 采用自连接的方式匹配员工和直属上级，使用OrganizationNode.GetAncestor(1)直接获取直属上级节点，比IsDescendantOf判断更精准高效
• 两次关联Person.Person表分别获取上级和员工的姓名信息
• WHERE条件直接对应需求的两个筛选规则，过滤出符合要求的匹配数据
• 用CONCAT函数拼接姓名兼容姓名字段为NULL的场景，比直接用+号拼接更稳妥

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