父子关系的 SQL 查询 [英] SQL Query for Parent Child Relationship

问题描述

我有父子关系的数据库表:

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

这里 parentId = 0 表示是根级节点.现在我想编写一个 SQL 查询，它将返回父类别的所有级别的子项.

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.

例如对于 nodeId = 1，它应该返回 3、4、5、6.

e.g. for nodeId = 1, it should return 3, 4, 5, 6.

我使用的是 MS SQL Server 2005

I am using MS SQL Server 2005

推荐答案

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]

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