SQL tree data struct(2): materialized path (1.2.3...)

EricYeung

于 2008-03-02 11:47:00 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： path tree struct sql insert output

本文链接：https://blog.csdn.net/EricYeung/article/details/2138892

SQL 专栏收录该内容

54 篇文章

订阅专栏

本文介绍了一种使用SQL查询来展示组织架构的方法，通过构建员工与上级之间的层级关系，实现了员工及其直接上级的快速检索。该方法利用了路径表示法，便于进行层次遍历和查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*
then materialized path representation of tree is easy to output: order by path, level
*/

declare @orgchart table (emp varchar(30), path varchar(100))

insert @orgchart
select 'Albert', '1'
union all select 'Bert', '1.2'
union all select 'Chuck', '1.3'
union all select 'Eddie', '1.3.4'
union all select 'Donna', '1.3.5'
union all select 'Fred' ,'1.3.6'

select *, lvl=len(path) - len(replace(path, '.', ''))
from @orgchart

-- show all the staffs and their boss
select staff=subordinate.emp, boss=supervisor.emp
from @orgchart subordinate, @orgchart supervisor
where subordinate.path like supervisor.path + '%'
and subordinate.path > supervisor.path

-- show all the staffs and their aide
-- select staff=supervisor.emp, subordinate.emp
select supervisor.*, subordinate.*
from @orgchart subordinate, @orgchart supervisor
where subordinate.path like supervisor.path + '%'
and subordinate.path > supervisor.path