SQL Server 查询树结构的表首节点和尾节点案例

一.创建表

DECLARE @t Table(id CHAR(1),parentId CHAR(1))
INSERT INTO @t values('a','')
INSERT INTO @t values('b','a')
INSERT INTO @t values('c','b')
INSERT INTO @t values('d','c')
INSERT INTO @t values('e','')
INSERT INTO @t values('f','e')
INSERT INTO @t values('g','f')

表中数据：

SELECT * FROM @t
/*
id    parentId
a     
b    a
c    b
d    c
e     
f    e
g    f
*/

二.查询首节点和尾节点

1）首节点

--首节点
SELECT * FROM @t A WHERE NOT EXISTS(
SELECT * FROM @t B WHERE a.parentId=b.id
) 
/*
id    parentId
a     
e     
*/

2）尾节点

--尾节点
SELECT * FROM @t A WHERE NOT EXISTS(
SELECT * FROM @t B WHERE a.id=b.parentId
) 
/*
id    parentId
d    c
g    f
*/