使用MSSQL查询获取父级的所有子级 [英] Getting all the children of a parent using MSSQL query

问题描述

我的数据库中有以下数据:

I have the following data in my database:

Parent      Child
101         102
101         103
101         104
101         105
101         106

我的参数是106.使用该参数，我想检索其父对象101下的所有其他子代.我尝试使用递归方法，但是在提供以下数据时它不起作用.还有另一种制定查询的方式吗?

My parameter is 106. And using the parameter I want to retrieve all the other children under its parent which is 101. I tried using the recursive method but it didn't work given the following data. Is there another way to formulate a query?

推荐答案

假设您要获取值为@p0的兄弟姐妹，则可以使用简单的自连接:

Assuming you want to get siblings of the value @p0, you can use a simple self-join:

SELECT p.Child
FROM Table1 c
INNER JOIN Table1 p ON c.Parent = p.Parent
WHERE c.Child = @p0
AND p.Child <> @p0

此处的不相等子句可确保您获得的兄弟姐妹不包括所搜索的值.视需要将其删除.

The not-equal clause here makes sure you get siblings not including the value you searched for. Remove it as necessary.

SQL Fiddle示例

但是，由于您提到了递归，也许您希望整个树从值@p0的父级开始.在这种情况下，您可以使用递归CTE:

Since you mention recursion though, perhaps you want the entire tree starting at the parent of the value @p0. In which case, you can use a recursive CTE:

WITH parent AS (
    SELECT Parent
    FROM Table1
    WHERE Child = @p0
), tree AS (
    SELECT x.Parent, x.Child
    FROM Table1 x
    INNER JOIN parent ON x.Parent = parent.Parent
    UNION ALL
    SELECT y.Parent, y.Child
    FROM Table1 y
    INNER JOIN tree t ON y.Parent = t.Child
)
SELECT Parent, Child
FROM tree

SQL Fiddle示例使用数据和 ，其中包含用于演示递归CTE的其他数据

SQL Fiddle examples using your data and with additional data to demonstrate the recursive CTE

这篇关于使用MSSQL查询获取父级的所有子级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！