create table tb(a varchar(50),b varchar(50))
insert into tb select 'A05','A06'
insert into tb select 'A06','A07'
insert into tb select 'A07','--'
insert into tb select 'A10','A11'
insert into tb select 'A11','A12'
insert into tb select 'A12','A05'select id=identity(int,1,1),* into # from tb where 1=2
insert into # select * from tb where a not in(select b from tb)
while @@rowcount>0
begin
insert into # select * from tb where a in(select b from # where id=@@identity)
endselect * from #id a b
1 A10 A11
2 A11 A12
3 A12 A05
4 A05 A06
5 A06 A07
6 A07 --递归
insert into tb select 'A05','A06'
insert into tb select 'A06','A07'
insert into tb select 'A07','--'
insert into tb select 'A10','A11'
insert into tb select 'A11','A12'
insert into tb select 'A12','A05'select id=identity(int,1,1),* into # from tb where 1=2
insert into # select * from tb where a not in(select b from tb)
while @@rowcount>0
begin
insert into # select * from tb where a in(select b from # where id=@@identity)
endselect * from #id a b
1 A10 A11
2 A11 A12
3 A12 A05
4 A05 A06
5 A06 A07
6 A07 --递归
-->生成测试数据
declare @tb table([字段一] nvarchar(3),[字段二] nvarchar(3))
Insert @tb
select N'A05',N'A06' union all
select N'A06',N'A07' union all
select N'A07',N'--' union all
select N'A10',N'A11' union all
select N'A11',N'A12' union all
select N'A12',N'A05'
Select * from @tb
order by
case when [字段二] not in (select [字段一] from @tb) then [字段一] else [字段二] end
/*
字段一 字段二
---- ----
A12 A05
A05 A06
A06 A07
A07 --
A10 A11
A11 A12
*/
declare @tb table([字段一] nvarchar(3),[字段二] nvarchar(3))
Insert @tb
select N'A05',N'A06' union all
select N'A06',N'A07' union all
select N'A07',N'--' union all
select N'A10',N'A11' union all
select N'A11',N'A12' union all
select N'A12',N'A05'
Select * from @tb
order by
case when [字段一] not in (select [字段二] from @tb) then [字段一] end desc /*
字段一 字段二
---- ----
A10 A11
A11 A12
A12 A05
A05 A06
A06 A07
A07 --
*/
Insert @tb
select N'A05',N'A06' union all
select N'A06',N'A07' union all
select N'A12',N'A05' union all
select N'A07',N'--' union all
select N'A10',N'A11' union all
select N'A11',N'A12' Select * from @tb
order by
case when [字段一] not in (select [字段二] from @tb) then [字段一] end desc 字段一 字段二
A10 A11
A11 A12
A05 A06
A06 A07
A12 A05
A07 --换下变这样了 哈哈
go
insert into tb select 'A05','A06'
insert into tb select 'A06','A07'
insert into tb select 'A07','--'
insert into tb select 'A10','A11'
insert into tb select 'A11','A12'
insert into tb select 'A12','A05'
godeclare @root varchar(50)
select @root=a from tb a where not exists(select 1 from tb where b=a.a);
with depth as
(
select * from tb where a=@root
union all
select a.* from tb a,depth b
where a.a=b.b
)
select * from depth
go
drop table tb
go