在单个 SQL 查询中查找多个父级的所有子级
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8204770/
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
Finding all children for multiple parents in single SQL query
提问by MvdD
Suppose I have a table with parent-child relationships.
假设我有一个包含父子关系的表。
parent child 1 4 1 5 2 6 3 7 4 8 6 9 7 10 8 11
Now I have a query that returns a list of people (eg. 1 and 2) and I want to find all their children, grandchildren etc. (in this case: 4, 5, 6, 8, 9, 11).
现在我有一个返回人员列表的查询（例如 1 和 2），我想找到他们所有的孩子、孙子等（在这种情况下：4、5、6、8、9、11）。
I know I can use common table expressions to search recursively, but I wondered if I could create a SQL statement to find all descendents at once without having to iterate over the input set.
我知道我可以使用公共表表达式进行递归搜索，但我想知道是否可以创建一个 SQL 语句来一次查找所有后代，而不必遍历输入集。
Edit:sorry for not being clear enough. I'm looking for something like:
编辑：抱歉不够清楚。我正在寻找类似的东西：
SELECT {Hierarchical relation} from table where parent in (1,2)
which should result in a single output column with rows for 4, 5, 6, 8, 9, 11.
这应该会导致单个输出列的行为 4、5、6、8、9、11。
I'm no longer interested in the relationship in the output, just the complete set of family members for multiple families.
我不再对输出中的关系感兴趣，只是对多个家庭的完整家庭成员集感兴趣。
回答by dani herrera
Here it is
这里是
---- PlainTable ----
parent idElement (child)
Null 1
1 4
1 5
2 6
3 7
4 8
6 9
7 10
8 11
WITH tableR (parent, idElement)
AS
(
-- Anchor member definition
SELECT e.parent, e.idElement
FROM PlainTable AS e
WHERE parent in (1,2)
UNION ALL
-- Recursive member definition
SELECT e.parent, e.idElement
FROM PlainTable AS e
INNER JOIN tableR AS d
ON e.parent = d.idElement
)
-- Statement that executes the CTE
SELECT idElement
FROM tableR --inner join to plain table by id if needed
回答by Brandon Moore
SQL Server 2008 has built in features to facilitate hierarchical data: http://msdn.microsoft.com/en-us/magazine/cc794278.aspx
SQL Server 2008 具有内置功能以促进分层数据：http: //msdn.microsoft.com/en-us/magazine/cc794278.aspx
I know I can use common table expressions to search recursively, but I wondered if I could create a SQL statement to find all descendents at once without having to iterate over the input set.
我知道我可以使用公共表表达式进行递归搜索，但我想知道是否可以创建一个 SQL 语句来一次查找所有后代，而不必遍历输入集。
I'm not sure what you mean by that. Most (maybe all?) CTE's can be accomplished through the use of subqueries, but using a subqueries wouldn't be any faster. When you say of you don't want to 'iterate' over the input set it sounds like you're talking about the use of cursors and of course you can do it as a set based operation (using CTEs or subqueries) but there's no way around the recursion.
我不确定你的意思。大多数（也许全部？）CTE 可以通过使用子查询来完成，但使用子查询不会更快。当您说您不想在输入集上“迭代”时，这听起来像是在谈论游标的使用，当然您可以将其作为基于集合的操作（使用 CTE 或子查询）来执行，但是没有绕过递归。
Edit: I'm sorry, I'm not thinking straight... of course you can't do recursion with normal subqueries but the point still stands that even if you could it wouldn't be faster. If you'd like to see strategies for doing recursion without CTE's then try searches for something like 'recursion sql 2000' since CTE's weren't around back then. Here are some examples: http://www.mssqltips.com/sqlservertip/938/recursive-queries-with-sql-server-2000/. Of course, the answer to your question remains the same though.
编辑：对不起，我没有直接思考......当然你不能用普通的子查询进行递归，但重点仍然是，即使你可以它也不会更快。如果您想了解在没有 CTE 的情况下进行递归的策略，请尝试搜索诸如“recursion sql 2000”之类的内容，因为那时 CTE 还没有出现。以下是一些示例：http: //www.mssqltips.com/sqlservertip/938/recursive-queries-with-sql-server-2000/。当然，您的问题的答案仍然相同。
回答by Reza ArabQaeni
using CTE
使用CTE
Recursive Queries Using Common Table Expressions
使用公用表表达式的递归查询
http://msdn.microsoft.com/en-us/library/ms186243.aspx
http://msdn.microsoft.com/en-us/library/ms186243.aspx
and
和
http://www.sqlservercentral.com/articles/T-SQL/65540/
http://www.sqlservercentral.com/articles/T-SQL/65540/
be can help you.
可以帮助你。
回答by Nonym
While waiting for an updated post:
在等待更新的帖子时：
SELECT DISTINCT
p.parent AS parent
, c.child AS child
, IFNULL(g.child, 'NONE') AS grandchild_of_parent
FROM parent_child as p
LEFT JOIN parent_child AS c ON p.parent = c.parent
LEFT JOIN parent_child AS g ON c.child = g.parent;
Results would look like this:
结果如下所示：
parent child grandchild_of_parent
1 4 8
1 5 NONE
2 6 9
3 7 10
4 8 11
6 9 NONE
7 10 NONE
8 11 NONE
Such a simple-minded-but-probably-harder-to-maintain type of code, but since I'm not familiar with SQL Server 2008's built in features to handle this type of request, I'll just throw a long shot...
这种头脑简单但可能更难维护的代码类型，但由于我不熟悉 SQL Server 2008 处理此类请求的内置功能，因此我将远投。 .
EDIT:
编辑：
Just so you can see results for yourself while you study common table expressionsand/or perhaps pivots... this will get your results, but only up to the great grandchildren of 1 and 2.
只是为了让您在学习common table expressions和/或可能时自己看到结果pivots……这将获得您的结果，但仅限于 1 和 2 的曾孙。
-- A. Parents 1 and 2
SELECT DISTINCT p.parent FROM parent_child AS p
WHERE p.parent IN (1,2)
UNION
-- B : Children of A
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (1,2)
UNION
-- C : Children of B, Grandchildren of A
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (1,2)
)
UNION
-- D : Children of C, Great-Grandchildren of A
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (1,2)
)
)
Again, I strongly suggest you study what the others have been posting.. and look into the links they provided. The inelegant query I provided you is not going to last long->it will absolutely FAILonce you have great-great-grandchildren.
再次，我强烈建议您研究其他人发布的内容……并查看他们提供的链接。我提供给你的不雅问不会持续很长时间->FAIL一旦你有曾曾孙，它绝对会持续。
回答by Darren
Typically I use a Nested Set Modelfor this and Yes you can have SQL do all the work for you, in fact you have SQL output XML that can be directly attached to .net treeview. Hope this helps
通常，我为此使用嵌套集模型，是的，您可以让 SQL 为您完成所有工作，实际上您拥有可以直接附加到 .net 树视图的 SQL 输出 XML。希望这可以帮助
Link: Is there a simple way to query the children of a node?
回答by MvdD
Ok, danihp's solution did put me on the right track. This is the solution I came up with:
好的，danihp 的解决方案确实让我走上了正轨。这是我想出的解决方案：
DECLARE @Input TABLE (
id int
)
INSERT INTO @Input VALUES (1),(2)
;WITH Relations (parent, child)
AS
(
SELECT e.parent, e.child
FROM RelationTable AS e
WHERE parent in (SELECT * FROM @Input)
UNION ALL
SELECT e.parent, e.child
FROM RelationTable AS e
INNER JOIN Relations AS d
ON e.parent = d.child
)
SELECT child
FROM Relations
It results in a list of child ids (excluding the 2 parent ids like I said earlier in the question): 4,5,6,8,9,11
它会生成一个子 ID 列表（不包括我在问题前面所说的 2 个父 ID）：4,5,6,8,9,11
回答by Rae Lee
---- PlainTable ----
parent idElement (child_id)
Null 1
1 4
1 5
2 6
3 7
4 8
6 9
7 10
8 11
**Table value function to get Child ids at 4(any) Level of parent in the same table:-**
FUNCTION fc_get_Child_IDs(Parent)
DECLARE @tbl TABLE (ID int)
DECLARE @level int=4
DECLARE @i int=1
insert into @tbl values (Parent)
while @i < @level
BEGIN
INSERT into @tbl
select child_id from PlainTable where Parent in (select ID from @tbl) and child_id not in (select ID from @tbl)
set @i = @i + 1
END
