MSSQL父子关系表的SQL查询(SQL Query for Parent Child Relationship)

建立一个数据表 Nodes

create table Nodes(NodeId int,NodeName nvarchar(50),ParentId int)

写入数据

insert into Nodes(NodeId ,NodeName ,ParentId)
values(1,'Node1',0),
(2,'Node2',0),
(3,'Node3',1),
(4,'Node4',1),
(5,'Node5',3),
(6,'Node6',5),
(7,'Node7',2)

数据表显示数据如下：

select * from Nodes

显示ParentId = 1 的全部层级数据

with [CTE] as (
    select nodeid,nodename,parentid from [nodes] c where c.[ParentId] = 1
    union all
    select c.nodeid,c.nodename,c.parentid from [CTE] p, [nodes] c where c.[ParentId] = p.[NodeId]
)
select * from [CTE]

 

显示nodeid = 1 的全部层级数据

with [CTE] as (
    select nodeid,nodename,parentid from [nodes] c where c.[NodeId] = 1
    union all
    select c.nodeid,c.nodename,c.parentid from [CTE] p, [nodes] c where c.[ParentId] = p.[NodeId]
)
select * from [CTE]

显示ParentId = 1 的全部层级数据和层级级别值

with [CTE] as (
    select nodeid,nodename,parentid, lev=1 from [nodes] c where c.parentid = 1
    union all
    select c.nodeid,c.nodename,c.parentid ,lev=p.lev + 1 from [CTE] p, [nodes] c where c.[ParentId] = p.[NodeId]
)
select * from [CTE]

 

显示NodeId = 1 的全部层级数据和层级级别值

with [CTE] as (
    select nodeid,nodename,parentid, lev=1 from [nodes] c where c.NodeId = 1
    union all
    select c.nodeid,c.nodename,c.parentid ,lev=p.lev + 1 from [CTE] p, [nodes] c where c.[ParentId] = p.[NodeId]
)
select * from [CTE]

 

 以上SQL语句在SQL2005 测试通过。