Create Table table1
(A Varchar(20))
Insert table1 Select '01'
Union All Select '0102'
Union All Select '010201'
Union All Select '0103'
Union All Select '010301'
Union All Select '01030102'
Union All Select '010302'
Union All Select '0104'
Union All Select '02'
Union All Select '0201'
GO
--得到最底層的節點
Select * From table1 A Where Not Exists(Select 1 From table1 Where A Like A.A+'%' And A<>A.A)
--得到最上層的節點
Select * From table1 A Where Not Exists(Select 1 From table1 Where A.A Like A+'%' And A<>A.A)
GO
Drop Table table1
GO
--Result
/*
A
010201
01030102
010302
0104
0201
Create Table table1
(A Varchar(20))
Insert table1 Select '01'
Union All Select '0102'
Union All Select '010201'
Union All Select '0103'
Union All Select '010301'
Union All Select '01030102'
Union All Select '010302'
Union All Select '0104'
GO
Select * From table1 A Where Not Exists(Select 1 From table1 Where A Like A.A+'%' And A<>A.A)
GO
Drop Table table1
GO
--Result
/*
A
010201
01030102
010302
0104
*/