如何通过SQL的N_LEVEL、N_LEFT/N_RIGHT查询并展示层级化数据？

现有一张表，包含ID_REF、N_LEFT、N_RIGHT、N_LEVEL、DISPLAY_NAME字段：

• N_LEVEL：存储节点的层级（1为根节点）
• N_LEFT/N_RIGHT：采用**嵌套集模型（Nested Set Model）**表示父子关系，父节点的N_LEFT小于所有子节点的N_LEFT，父节点的N_RIGHT大于所有子节点的N_RIGHT

需要编写SQL查询，生成包含level_1、level_2、level_3的结果集，明确每个节点对应的各级父节点名称，最终输出需匹配给定的期望结果。

示例表数据 #

ID_REF    N_LEFT    N_RIGHT    N_LEVEL    DISPLAY_NAME
5854      97        120        1          Students    
5855      98        113        2          Bachelors   
5856      114       115        2          Masters     
440684    105       106        3          2020        
60091     99        102        3          2018        
579034    118       119        2          PhD-DSc     
182954    100       101        4          WithContract
245186    103       104        3          2019        
413700    116       117        2          TKDorm      
694720    107       108        3          2021        
729020    109       110        3          2021Add     
855029    111       112        3          2022     

期望查询结果 #

level_1     level_2     level_3     N_LEFT    N_RIGHT    N_LEVEL    DISPLAY_NAME
NULL        NULL        NULL        97        120        1          Students    
Students    NULL        NULL        98        113        2          Bachelors   
Students    NULL        NULL        114       115        2          Masters     
Students    Bachelors   NULL        105       106        3          2020        
Students    Bachelors   NULL        99        102        3          2018        
Students    NULL        NULL        118       119        2          PhD-DSc 
Students    Bachelors   NULL        103       104        3          2019        
Students    NULL        NULL        116       117        2          TKDorm      
Students    Bachelors   NULL        107       108        3          2021        
Students    Bachelors   NULL        109       110        3          2021Add     
Students    Bachelors   NULL        111       112        3          2022  

尝试的代码 #

SELECT s.ID_REF, s.N_LEFT, s.N_RIGHT, s.N_LEVEL, s.DISPLAY_NAME
FROM SUBDIV_REF S
WHERE ID_REF =5854
and N_left >=97 AND N_right <=120
-- AND n_level=
--create table roles ( id int not null, parentId int, roleName varchar(50) not null );
DECLARE @roles TABLE(id int not null,
    N_LEFT int,
    N_RIGHT int,
    N_LEVEL int,
    DISPLAY_NAME varchar(50))

insert into @roles
    (id, N_LEFT, N_RIGHT,N_LEVEL,DISPLAY_NAME)
values
    (1, 97 , 120 , 1 , 'Students'),
    (2, 98 , 113 , 2 , 'Bachelors'),
    (3, 114 , 115 , 2 , 'Masters'),
    (4, 105 , 106 , 3 , '2020' ),
    (5, 99 , 102 , 3 , '2018'),
    (6, 118 , 119 , 2 , 'PhD-DSc'),
    (7, 103 , 104 , 3 , '2019'),
    (8, 116 , 117 , 2 , 'TKDorm'),
    (9, 107 , 108 , 3 , '2021'),
    (10, 109 , 110 , 3 , '2021Add'),
    (11, 111 , 112 , 3 , '2022')

select * from @roles

解决方案 #

针对嵌套集模型的层级查询，通过自连接匹配父节点范围条件获取各级父节点名称，SQL代码如下：

SELECT
    -- 获取层级1的父节点名称
    MAX(CASE WHEN p1.N_LEVEL = 1 THEN p1.DISPLAY_NAME END) AS level_1,
    -- 获取层级2的父节点名称
    MAX(CASE WHEN p2.N_LEVEL = 2 THEN p2.DISPLAY_NAME END) AS level_2,
    -- 获取层级3的父节点名称
    MAX(CASE WHEN p3.N_LEVEL = 3 THEN p3.DISPLAY_NAME END) AS level_3,
    s.N_LEFT,
    s.N_RIGHT,
    s.N_LEVEL,
    s.DISPLAY_NAME
FROM SUBDIV_REF s
-- 匹配层级1的父节点（范围包含当前节点）
LEFT JOIN SUBDIV_REF p1 ON p1.N_LEFT < s.N_LEFT AND p1.N_RIGHT > s.N_RIGHT AND p1.N_LEVEL = 1
-- 匹配层级2的父节点（范围包含当前节点）
LEFT JOIN SUBDIV_REF p2 ON p2.N_LEFT < s.N_LEFT AND p2.N_RIGHT > s.N_RIGHT AND p2.N_LEVEL = 2
-- 匹配层级3的父节点（范围包含当前节点）
LEFT JOIN SUBDIV_REF p3 ON p3.N_LEFT < s.N_LEFT AND p3.N_RIGHT > s.N_RIGHT AND p3.N_LEVEL = 3
GROUP BY s.N_LEFT, s.N_RIGHT, s.N_LEVEL, s.DISPLAY_NAME
ORDER BY s.N_LEFT;

代码说明 #

1. 自连接逻辑：利用嵌套集模型核心规则——父节点N_LEFT小于子节点N_LEFT、父节点N_RIGHT大于子节点N_RIGHT，通过三次自连接分别匹配对应层级的父节点。
2. CASE聚合：用MAX(CASE...)确保每个节点仅返回对应层级的父节点名称，无匹配时返回NULL。
3. 分组排序：按当前节点核心字段分组，最终按N_LEFT排序保证结果顺序与示例一致。

若使用临时表@roles测试，只需将SUBDIV_REF替换为@roles即可。

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