SQL 迭代查询语句

2020-03-11 15:09:24 cnblogs-Twang 数据库百度已收录

本文摘自cnblogs-Twang，原文连接：https://www.cnblogs.com/wangfuyou/p/7615020.html，侵删。

SQL 迭代查询语句

-- Get childs by parent id 按父id获取子项
WITH Tree
AS
(
    SELECT Id,ParentId FROM dbo.Node P WHERE P.Id = 21 -- parent id
    UNION ALL
    SELECT C.Id,C.ParentId FROM dbo.Node C
    INNER JOIN Tree T ON C.ParentId = T.Id
)
SELECT * FROM Tree

-- Get parents by child id 按子id获取父级
WITH Tree
AS
(
    SELECT Id,ParentId FROM dbo.Node C WHERE C.Id = 57 -- child id
    UNION ALL
    SELECT P.Id,P.ParentId FROM dbo.Node P
    INNER JOIN Tree T ON P.Id = T.ParentId
)
SELECT * FROM Tree