SQL Server：如何在自引用表中获取给定父 ID 的所有子记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1686340/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: How to get all child records given a parent id in a self referencing table
提问by Matthew Dresser
Hi I have a table which references itself and I need to be able to select the parent and all it's child records from a given parent Id.
嗨，我有一个引用自身的表，我需要能够从给定的父 ID 中选择父记录及其所有子记录。
My table is as follows:
我的表如下：
ID | ParentID | Name
-----------------------
1 NULL A
2 1 B-1
3 1 B-2
4 2 C-1
5 2 C-2
So for the above example I'd like to be able to pass in a value of 1 and get all the records above.
所以对于上面的例子，我希望能够传入 1 的值并获得上面的所有记录。
So far, I've come up with the following recursive table-valued-function but it's not behaving as expected (only returning the first record).
到目前为止，我已经提出了以下递归表值函数，但它没有按预期运行（仅返回第一条记录）。
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
回答by Adriaan Stander
You can try this
你可以试试这个
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
回答by Abtin Forouzandeh
Unless you are using Oracle, your table structure is not suitable for the problem described. What you are attempting to do is grab a hierarchy (traversing a tree structure).
除非您使用 Oracle，否则您的表结构不适合所描述的问题。您试图做的是获取层次结构（遍历树结构）。
There is an article, More Trees & Hierarchies in SQL, that describes one method of solving the hierarchy problem. He basically adds a "lineage" column describing the hierarchy to every row.
有一篇文章More Trees & Hierarchies in SQL描述了一种解决层次结构问题的方法。他基本上为每一行添加了一个描述层次结构的“血统”列。
回答by Ankush Madankar
Recursion in CTElooks bit expensive, so I have wrote this function which make use of recursive function call but much faster that CTErecursion.
递归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
