MSSQL树型结构/获得上级及上上级及获得下级及下下级

最新推荐文章于 2021-07-23 17:33:23 发布

原创最新推荐文章于 2021-07-23 17:33:23 发布 · 689 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

SQL语句专栏收录该内容

20 篇文章

订阅专栏

-- 获得上级及上上级
with cte as(select id,name,parent_id,0 as lvl from catelog where id = '1001'
union all
select d.id,d.name,d.parent_id,lvl + 1 from cte c inner join catelog d on c.parent_id = d.id)

select * from cte

-- 获得下级及下下级
with cte as(select id,name,parent_id,0 as lvl from catelog where id = '1001'
union all
select d.id,d.name,d.parent_id,lvl + 1 from cte c inner join catelog d on c.id = d.parent_id)

select * from cte