如何在MSSQL查询中查找所有父节点

针对你提供的树形结构表foo，我们可以用**递归CTE（公共表表达式）**来高效查询任意节点的所有父节点，这是MSSQL处理层级数据的常用方案。

首先先确认你的表结构和初始化数据（我帮你补全了最后一条插入语句）：

CREATE TABLE foo ( 
    id int IDENTITY(1,1) PRIMARY KEY, 
    parent_no int DEFAULT 0, 
    name varchar(255) 
); 

INSERT INTO foo (name, parent_no) VALUES ('Locations', 0);
INSERT INTO foo (name, parent_no) VALUES ('NSW', 1);
INSERT INTO foo (name, parent_no) VALUES ('QLD', 1);
INSERT INTO foo (name, parent_no) VALUES ('VIC', 1);
INSERT INTO foo (name, parent_no) VALUES ('Sydney', 2);
INSERT INTO foo (name, parent_no) VALUES ('Brisbane', 3);
INSERT INTO foo (name, parent_no) VALUES ('Melbourne', 4);

核心解决方案：递归CTE #

递归CTE分为两部分：锚点成员（起始查询）和递归成员（循环向上查找父节点）。下面以查询Sydney（id=5）的所有父节点为例：

WITH ParentHierarchy AS (
    -- 锚点成员：先定位目标节点
    SELECT 
        id, 
        parent_no, 
        name,
        1 AS Level -- 标记当前层级，方便看深度
    FROM foo
    WHERE id = 5 -- 这里替换成你要查询的目标节点id
    
    UNION ALL
    
    -- 递归成员：向上查找父节点，直到根节点（parent_no=0）
    SELECT 
        f.id, 
        f.parent_no, 
        f.name,
        ph.Level + 1 AS Level
    FROM foo f
    INNER JOIN ParentHierarchy ph ON f.id = ph.parent_no
    WHERE f.parent_no != 0 -- 根节点的parent_no是0，查到这里停止
)
-- 最终查询所有父节点（如果想包含目标节点本身，直接SELECT *即可；如果只需要父节点，加WHERE Level > 1）
SELECT * FROM ParentHierarchy
ORDER BY Level DESC; -- 从根节点到目标节点的顺序展示

结果说明 #

执行上面的语句后，你会得到如下结果：

如果只需要目标节点的父节点（不包含自身），可以把最后查询改成：

SELECT * FROM ParentHierarchy WHERE Level > 1 ORDER BY Level DESC;

通用化查询（通过参数指定目标节点） #

如果你想通过参数动态指定要查询的节点，可以定义一个变量：

DECLARE @TargetId INT = 6; -- 比如查询Brisbane的父节点

WITH ParentHierarchy AS (
    SELECT id, parent_no, name, 1 AS Level
    FROM foo
    WHERE id = @TargetId
    
    UNION ALL
    
    SELECT f.id, f.parent_no, f.name, ph.Level + 1
    FROM foo f
    INNER JOIN ParentHierarchy ph ON f.id = ph.parent_no
    WHERE f.parent_no != 0
)
SELECT * FROM ParentHierarchy ORDER BY Level DESC;

注意事项 #

• 如果你的表可能存在循环引用（比如某个节点的parent_no指向自己或后代节点），可以在递归成员中加上AND f.id NOT IN (SELECT id FROM ParentHierarchy)来防止无限递归。
• 根节点的parent_no是0，所以我们在递归条件里判断f.parent_no != 0来终止递归，确保不会重复查询根节点以上的无效数据。

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