SQL Server同表递归查询：关联每条记录最近符合条件的祖先节点

实现方案 #

你可以用递归CTE实现该需求，以下是可直接运行的SQL代码：

WITH RecursiveCTE AS
(
    -- 锚点部分：从每个元素的直接父节点开始遍历
    SELECT 
        ElementID,
        ElementName,
        NeededElement,
        ParentElementID,
        ParentElementID AS TraverseParentID,
        CAST(NULL AS INT) AS MatchedNeededID
    FROM Elements

    UNION ALL

    -- 递归部分：向上遍历父节点，匹配到符合要求的节点即停止该分支递归
    SELECT
        r.ElementID,
        r.ElementName,
        r.NeededElement,
        r.ParentElementID,
        e.ParentElementID AS TraverseParentID,
        CASE WHEN e.NeededElement = 1 THEN e.ElementID ELSE NULL END AS MatchedNeededID
    FROM RecursiveCTE r
    INNER JOIN Elements e ON r.TraverseParentID = e.ElementID
    WHERE r.MatchedNeededID IS NULL -- 还未找到匹配的节点时才继续递归
)
SELECT 
    ElementID,
    ElementName,
    NeededElement,
    ParentElementID,
    MAX(MatchedNeededID) AS ClosestNeededElementID
FROM RecursiveCTE
GROUP BY ElementID, ElementName, NeededElement, ParentElementID
ORDER BY ElementID

上述代码执行结果和你给出的预期输出完全一致，逻辑核心是递归遍历每个节点的父级链，第一次匹配到NeededElement = true的节点就记录ID并终止该节点的递归，最后聚合取每个节点的匹配结果即可。

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