SQL Server：如何在自引用表中，根据父级ID获取所有子记录？

Question

嗨，我有一张引用其自身的表格，并且我需要能够从给定的父级ID选择父级及其所有子记录。

我的表格如下所示：

ID   | ParentID | Name         
-----------------------              
1      NULL       A
2      1          B-1
3      1          B-2
4      2          C-1
5      2          C-2

对于上面的示例，我希望能够传入值1并获取所有记录。

到目前为止，我想到了以下递归表值函数，但它没有按预期工作（只返回第一条记录）。

CREATE FUNCTION [dbo].[SelectBranches]
(   
    @id INT
    ,@parentId INT
)
RETURNS @branchTable TABLE
(
    ID INT
    ,ParentID INT
    ,Name INT
)
AS
BEGIN 

    IF @branchId IS NOT NULL BEGIN

        INSERT INTO @branchTable
        SELECT 
            ID
            ,ParentID
            ,Name
        FROM
            tblLinkAdvertiserCity
        WHERE
            ID = @id

    END

    INSERT INTO @branchTable
    SELECT
        br.ID
        ,br.ParentID
        ,br.Name
    FROM
        @branchTable b
    CROSS APPLY
        dbo.SelectBranches(NULL, b.ParentID) br

    RETURN
END
GO

- Matthew Dresser

---

在向这里提问之前，尝试自己解决问题是值得肯定的。+1 - iMatoria

Answer 1

你可以尝试这个。

DECLARE @Table TABLE(
        ID INT,
        ParentID INT,
        NAME VARCHAR(20)
)

INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 1, NULL, 'A'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 2, 1, 'B-1'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 3, 1, 'B-2'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 4, 2, 'C-1'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 5, 2, 'C-2'


DECLARE @ID INT

SELECT @ID = 2

;WITH ret AS(
        SELECT  *
        FROM    @Table
        WHERE   ID = @ID
        UNION ALL
        SELECT  t.*
        FROM    @Table t INNER JOIN
                ret r ON t.ParentID = r.ID
)

SELECT  *
FROM    ret

- Adriaan Stander

---

@Robin Day：默认值为100，您可以在查询末尾添加“WITH MAXRECURSION number”来更改它。数字0表示没有限制。 - Andomar

MAXRECURSION提示的值介于0和32,767之间。 - Adriaan Stander

@astander，您是救星。如果可以的话，我会给您约+100个赞！ - Matthew Dresser

有没有办法传递额外的参数，比如“级别/深度”，并且仅限于该参数？ - Billa

这就是我要找的... 对我有用。谢谢！ - Bhavik Patel

Answer 2

CTE 中的递归看起来有点昂贵，因此我编写了这个函数，它利用递归函数调用，但比 CTE 递归快得多。

CREATE FUNCTION [dbo].[Fn_GetSubCategories]
(
@p_ParentCategoryId INT
) RETURNS @ResultTable TABLE 
(   
    Id INT
)
AS
BEGIN
--Insert first level subcategories.
INSERT INTO @ResultTable 
SELECT Id FROM Category WHERE ParentCategoryId = @p_ParentCategoryId OR Id = @p_ParentCategoryId

DECLARE @Id INT
DECLARE @ParentCategory TABLE(Id INT)

DECLARE cur_categories CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY FOR 
SELECT Id FROM Category WHERE ParentCategoryId = @p_ParentCategoryId and Id != @p_ParentCategoryId
OPEN cur_categories
IF @@CURSOR_ROWS > 0
     BEGIN 
     FETCH NEXT FROM cur_categories INTO @Id
     WHILE @@FETCH_STATUS = 0
     BEGIN
        --Insert remaining level sub categories.
        IF EXISTS(SELECT 1 FROM Category WHERE ParentCategoryId = @Id AND Id != @Id)
        BEGIN
            INSERT INTO @ResultTable
            SELECT DISTINCT C.Id from Fn_GetSubCategories(@Id) C INNER JOIN @ResultTable R ON C.Id != R.Id
        END

     FETCH NEXT FROM cur_categories INTO @Id
     END

     --Delete duplicate records
     ;WITH CTE AS
     (SELECT *,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) AS RN FROM @ResultTable)
     DELETE FROM CTE WHERE RN<>1

END
CLOSE cur_categories
DEALLOCATE cur_categories

RETURN

END

- Ankush Madankar

Answer 3

除非您使用Oracle，否则您的表结构不适用于所描述的问题。您试图做的是获取层次结构（遍历树形结构）。

有一篇文章SQL中的更多树和层次结构，描述了解决层次结构问题的一种方法。他基本上为每一行添加了一个描述层次结构的“血统”列。

- Abtin Forouzandeh

---

问题说他正在使用 SQL Server 2005 ;) - Andomar

很抱歉，@Abtin。我正在使用一个遗留系统，所以完全不能更改数据库架构 :( - Matthew Dresser