选择语句返回父级和无限子级

问题描述

给出表结构，如下所示:

Give the table structure, as something like:

ID      ParentID      Name
1       NULL          A root
2       NULL          Another root
3       1             Child of 1
4       3                Grandchild of 1
5       4                   Great grandchild of 1
6       1             Child of 1
7       NULL          Another root
8       7             Child of 6

我正在寻找一个简单的Sql语句/函数(如果可能的话)的解决方案，当给定ID = 1

I am looking for an elegant (if possible) solution for a single Sql statement/function that would return all data in the table when given an ID = 1

所以我的结果将类似于:

So my result would look something like:

ID      ParentID      Name
1       NULL          A root
3       1             Child of 1
4       3                Grandchild of 1
5       4                   Great grandchild of 1
6       1             Child of 1

我在SO上也看到过类似的问题，尽管在大多数情况下，它们似乎只是在关注给定数量的级别.

I've seen similar questions on SO though for the most part they only seem to be looking at a given number of levels.

这种结构最终可能是无限的-带有孩子的文件夹以及其他很多孩子

This structure can, ultimately, be limitless - folder with children, with many other children

这可能吗?如果是这样，我该怎么做?

Is this possible? If so, how would I accomplish it?

推荐答案

因此请参考以下答案:

Sql Server CTE父子递归

Sql Server CTE Parent Child recursive

以下是与您的架构相关的工作版本:

Here's a working version with your schema:

CREATE TABLE YOUR_TABLE
    ([ID] int, [ParentID] int, [Name] varchar(21))
;

INSERT INTO YOUR_TABLE
    ([ID], [ParentID], [Name])
VALUES
    (1, NULL, 'A root'),
    (2, NULL, 'Another root'),
    (3, 1, 'Child of 1'),
    (4, 3, 'Grandchild of 1'),
    (5, 4, 'Great grandchild of 1'),
    (6, 1, 'Child of 1'),
    (7, NULL, 'Another root'),
    (8, 7, 'Child of 6')
;

递归CTE

DECLARE @ID INT = 1

;WITH ParentChildCTE
AS (
    SELECT ID, ParentId, Name
    FROM YOUR_TABLE
    WHERE Id = @ID

    UNION ALL

    SELECT T1.ID, T1.ParentId, T1.Name
    FROM YOUR_TABLE T1
    INNER JOIN ParentChildCTE T ON T.ID = T1.ParentID
    WHERE T1.ParentID IS NOT NULL
    )
SELECT *
FROM ParentChildCTE

关键部分是CTE的创建，其中UNION ALL重新连接到结果集，将ID连接到ParentId，这不限制级别的数量.

The key part is in the CTE creation where the UNION ALL joins back on to the result set, joining ID to ParentId, which doesn't limit the number of level.

这篇关于选择语句返回父级和无限子级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！