select * from s s1
inner join s s2 where s2.id not in( s1.id ) and s2.id = s1.fr_id
inner join s s3 where s3.id not in( s1.id, s2.id ) and s3.id = s2.fr_id
inner join s s4 where s4.id not in( s1.id, s2.id, s3.id ) and s4.id = s3.fr_id
...
inner join s s10 where s10.id not in( s1.id, s2.id, s3.id,....s9.id ) and s10.id = s9.fr_id
用中间临时表 t
insert t(id)
select distinct id from s
where s.id not in (
select id from t
) and s.id in (
select s2.fr_id from s s2
inner join t where t.id= s2.id
)
如果任何记录永远存在id<>fr_id, 可以简化:
select * from s s1
inner join s s2 where s2.id = s1.fr_id
inner join s s3 where s3.id not in( s1.id ) and s3.id = s2.fr_id
inner join s s4 where s4.id not in( s1.id, s2.id ) and s4.id = s3.fr_id
...
inner join s s10 where s10.id not in( s1.id, s2.id, s3.id,....s8.id ) and s10.id = s9.fr_id
where not exist (
select * from s s11 where s11.id not in( s1.id, s2.id, s3.id,....s9.id ) and s11.id = s10.fr_id
)