表ID Has PID
1 N 0
2 N 1
3 Y 1
4 N 1
5 Y 2
6 Y 2
7 N 2
8 Y 6
9 N 4
10 N 8
11 Y 8
12 N 10
13 Y 3
14 Y 10
15 N 11
比较效率的得到ID Has PID
1 N 0
2 N 1
3 Y 1
5 Y 2
6 Y 2
8 Y 6
10 N 8
11 Y 8
13 Y 3
14 Y 10
说简单点，就是一棵果树，把没有果子的枝子剪掉。
1 N 0
2 N 1
3 Y 1
4 N 1
5 Y 2
6 Y 2
7 N 2
8 Y 6
9 N 4
10 N 8
11 Y 8
12 N 10
13 Y 3
14 Y 10
15 N 11
比较效率的得到ID Has PID
1 N 0
2 N 1
3 Y 1
5 Y 2
6 Y 2
8 Y 6
10 N 8
11 Y 8
13 Y 3
14 Y 10
说简单点，就是一棵果树，把没有果子的枝子剪掉。
create table ait
(ID int, Has char(1), PID int)insert into ait
select 1, 'N', 0 union all
select 2, 'N', 1 union all
select 3, 'Y', 1 union all
select 4, 'N', 1 union all
select 5, 'Y', 2 union all
select 6, 'Y', 2 union all
select 7, 'N', 2 union all
select 8, 'Y', 6 union all
select 9, 'N', 4 union all
select 10, 'N', 8 union all
select 11, 'Y', 8 union all
select 12, 'N', 10 union all
select 13, 'Y', 3 union all
select 14, 'Y', 10 union all
select 15, 'N', 11
;with t as
(select ID,PID from ait where Has='Y'
union all
select a.ID,a.PID from ait a inner join t on a.ID=t.PID
)
select t2.*
from
(select distinct ID from t) t1
inner join ait t2 on t1.ID=t2.ID-- 结果
ID Has PID
----------- ---- -----------
1 N 0
2 N 1
3 Y 1
5 Y 2
6 Y 2
8 Y 6
10 N 8
11 Y 8
13 Y 3
14 Y 10(10 row(s) affected)