sql递归 with查询所有上级或所有下级

最新推荐文章于 2024-07-21 04:28:15 发布

邵_金波

最新推荐文章于 2024-07-21 04:28:15 发布

阅读量1.5w

点赞数 4

分类专栏： SqlServer

本文链接：https://blog.csdn.net/qq395537505/article/details/78902032

版权

SqlServer 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

drop table #Area;

CREATE TABLE #Area
(
    id INT  NOT NULL,
    city_name NVARCHAR(100) NOT NULL,
    parent_id INT NOT NULL
)
INSERT INTo #Area(id,city_name,parent_id) VALUES(1,'江苏省',0)
INSERT INTo #Area(id,city_name,parent_id) VALUES(2,'常州市',1)
INSERT INTo #Area(id,city_name,parent_id) VALUES(3,'天宁区',2)
INSERT INTo #Area(id,city_name,parent_id) VALUES(4,'新北区',2)
INSERT INTo #Area(id,city_name,parent_id) VALUES(5,'天宁区_1',3)
INSERT INTo #Area(id,city_name,parent_id) VALUES(6,'新北区_1',4)

-------------------------------------------------
--根据节点ID获取所有子节点
-------------------------------------------------
SELECT * FROM #Area;
WITH AreaTree AS 
(
    SELECT * from #Area where id = 1--需要查找的节点
    UNION ALL 
    SELECT #Area.* from AreaTree
    JOIN #Area on AreaTree.id = #Area.parent_id
)
SELECT * FROM AreaTree;
-------------------------------------------------
--根据节点ID获取所有父节点
-------------------------------------------------
SELECT * FROM #Area;
with AreaTree AS
(
    SELECT * from #Area where Id=6 --需要查找的节点
    UNION ALL 
    SELECT #Area.* from AreaTree
    JOIN #Area on AreaTree.parent_id= #Area.Id
)
SELECT * from AreaTree;