父子关系的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/207309/
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 Query for Parent Child Relationship
提问by Programmer
I have db table with parent child relationship as:
我有父子关系的数据库表：
NodeId NodeName ParentId
------------------------------
1 Node1 0
2 Node2 0
3 Node3 1
4 Node4 1
5 Node5 3
6 Node6 5
7 Node7 2
Here parentId = 0 means that it is a root level node. Now I want to write an SQL Query which will return child at all level of a parent category.
这里 parentId = 0 表示它是根级节点。现在我想编写一个 SQL 查询，它将在父类别的所有级别返回子级。
e.g. for nodeId = 1, it should return 3, 4, 5, 6.
例如，对于 nodeId = 1，它应该返回 3、4、5、6。
I am using MS SQL Server 2005
我正在使用 MS SQL Server 2005
采纳答案by yfeldblum
with [CTE] as (
select * from [TheTable] c where c.[ParentId] = 1
union all
select * from [CTE] p, [TheTable] c where c.[ParentId] = p.[NodeId]
)
select * from [CTE]
回答by Keith Palmer Jr.
You should look into using the Nested Set Model for parent-child relationships within an SQL database. It's much nicer than trying to store the parentID of records in the table like this, and makes queries like this much easier.
您应该考虑使用嵌套集模型来处理 SQL 数据库中的父子关系。这比像这样尝试将记录的 parentID 存储在表中要好得多，并且使这样的查询更容易。
回答by Coolcoder
And just to make sure it works if its a parent of itself (otherwise it will recurse until it breaks):
并且只是为了确保它在它自己的父级时工作（否则它会递归直到它中断）：
with [CTE] as (
select * from [TheTable] c where c.[ParentId] = 1
union all
select * from [CTE] p, [TheTable] c where c.[ParentId] = p.[NodeId]
and c.[ParentId] <> c.[NodeId]
)
select * from [CTE]
回答by Raju
WITH Temp_Menu AS
(
SELECT AM.* from FCB_AccessMenu AM where AM.[ParentId] = 6
UNION ALL
SELECT AM.* FROM FCB_AccessMenu AM ,Temp_Menu TM WHERE AM.[ParentID]=TM.[MenuID]
)
SELECT * FROM Temp_Menu ORDER BY ParentID
