SQL Server:构建有效树过滤无效分支 [英] SQL Server : build valid tree filtering invalid branches

问题描述

我有一个包含以下数据的表格:

I have a table with following data:

ID     ParentID    Name
-----------------------
 1       NULL      OK1
 2        1        OK2
 3        2        OK3
 5        4        BAD1
 6        5        BAD2

所以我只需要取那些链接到 ParentID = NULL 或这些行的有效子行的行(即:OK3 是有效的，因为它链接到 OK2，后者链接到 OK1，链接到NULL，这是有效的.)

So I need to take only those lines, which are linked to ParentID = NULL OR valid children of such lines (i.e: OK3 is valid because it's linked to OK2, which is linked to OK1, which is linked to NULL, which is valid.)

但是 BAD1 和 BAD 2 是无效的，因为它们没有链接到一行，而行链接到 NULL.

But BAD1 and BAD 2 are not valid because those are not linked to a line, which is linked to NULL.

我想出的最佳解决方案是过程 + 函数.并且函数被调用的次数与表中的最大链接级别数相同.

The best solution I figured out is a procedure + function. And function is called as many times as the max number of link levels in the table.

有人可以为此类任务提出更好的解决方案吗?

Can anybody suggest better solution for such task?

推荐答案

您需要的只是爱和基本的 递归 CTE :-)

All you need is love, and a basic recursive CTE :-)

创建并填充示例数据(请在以后的问题中保存我们这一步):

Create and populate sample data (Please save us this step in future questions):

DECLARE @T as table
(
    ID int,
    ParentID int,
    Name varchar(4)
)

INSERT INTO @T VALUES
(1, NULL, 'OK1'),
(2, 1, 'OK2'),
(3, 2, 'OK3'),
(5, 4, 'BAD1'),
(6, 5, 'BAD2')

CTE 和查询:

;WITH CTE AS
(
    SELECT ID, ParentId, Name
    FROM @T 
    WHERE ParentId IS NULL

    UNION ALL

    SELECT T1.ID, T1.ParentId, T1.Name
    FROM @T T1
    INNER JOIN CTE T2 ON T1.ParentID = T2.ID
)

SELECT *
FROM CTE 

结果:

ID          ParentId    Name
----------- ----------- ----
1           NULL        OK1
2           1           OK2
3           2           OK3

这篇关于SQL Server:构建有效树过滤无效分支的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！