SQL Server如何基于岗位层级规则实现两表条件关联查询

核心问题对齐 #

你当前的基础查询存在两个基础缺陷：

• 错误引用了Offices表不存在的Position字段，管理岗名称需要通过Offices的PositionID关联Positions表获取
• 没有按照层级规则做拆分匹配，仅做了两表的简单等值关联，完全没有覆盖上下级的管辖范围判断逻辑

方案1：无表结构改动的实时查询实现 #

按照定义的层级规则，直接拆分两类管辖范围做结果拼接，可1:1匹配示例输出：

1. Level 0最高岗（Supervisor）：管辖同部门下所有Level > 0的岗位，不限制Section、岗位序列
2. Level 1中层岗（Senior类）：管辖同部门下、同岗位序列的Level 2执行层岗位
3. Level 2执行岗：无下辖岗位，不需要生成关联映射

对应的SQL Server版本查询代码：

-- 先整理所有管理岗的基础信息，提取所属部门、Section、层级、岗位序列
WITH ManagePositionBase AS (
    SELECT 
        O.Department,
        O.Section,
        P_manage.id AS ManagePositionID,
        P_manage.PosName AS ManagingPosition,
        P_manage.Level AS ManageLevel,
        -- 提取岗位核心序列，剥离层级前缀做匹配
        TRIM(REPLACE(REPLACE(P_manage.PosName, 'Senior', ''), 'Junior', '')) AS PositionSeries
    FROM Offices O
    INNER JOIN Positions P_manage 
        ON O.PositionID = P_manage.id
)
-- 匹配Level0最高岗的管辖范围
SELECT 
    MPB.Department,
    MPB.Section,
    MPB.ManagingPosition,
    P_sub.PosName AS Position,
    P_sub.id AS PositionID
FROM ManagePositionBase MPB
-- 关联被管辖岗位所属的部门信息
INNER JOIN Positions P_sub
    ON P_sub.Level > 0
INNER JOIN Offices O_sub
    ON P_sub.OfficeID = O_sub.id
WHERE MPB.ManageLevel = 0
    AND O_sub.Department = MPB.Department -- 同部门边界
UNION ALL
-- 匹配Level1中层岗的管辖范围
SELECT 
    MPB.Department,
    MPB.Section,
    MPB.ManagingPosition,
    P_sub.PosName AS Position,
    P_sub.id AS PositionID
FROM ManagePositionBase MPB
INNER JOIN Positions P_sub
    ON P_sub.Level = 2
    -- 匹配同岗位核心序列
    AND TRIM(REPLACE(REPLACE(P_sub.PosName, 'Senior', ''), 'Junior', '')) = MPB.PositionSeries
INNER JOIN Offices O_sub
    ON P_sub.OfficeID = O_sub.id
WHERE MPB.ManageLevel = 1
    AND O_sub.Department = MPB.Department
-- 按示例输出顺序排序
ORDER BY MPB.Department, MPB.Section, MPB.ManageLevel

注意：如果测试数据出现岗位所属Section错位（比如示例中Junior Dev跨Section归属Developer序列管理），只要保证岗位核心序列匹配逻辑不变，仅需调整关联的过滤条件即可，不需要改动整体查询框架。

方案2：面向Web权限场景的长期优化 #

如果要把这套逻辑用于Web端的权限控制，长期来看不建议每次请求都实时计算管辖范围，会带来不必要的性能损耗，可以通过表结构优化落地：

• 新增PositionPermissionMap映射表，存储两个核心字段：ManagingPositionID（管理岗的Positions主键）、SubordinatePositionID（被管辖岗位的Positions主键），设置两个字段为联合主键避免重复记录
• 当Offices、Positions表的配置变更时，用上面的实时查询逻辑把映射关系预计算好存入这张表，加上合适的索引
• 实际做权限校验时，只要拿到用户绑定的岗位ID，直接关联这张映射表就能快速拿到所有可查看的下属岗位范围，查询性能可以提升一个量级，适配高并发的Web访问场景

落地校验点 #

最终生成的映射关系需要符合三个规则：

• Level 0的Supervisor可以覆盖同部门下所有Level1、Level2的岗位，没有遗漏
• Level1的Senior岗只覆盖自己对应序列的Level2 Junior岗，不会跨序列越权
• Level2的执行岗不会生成任何下辖的映射关系，符合最底层岗位的权限定位

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