日期:2014-05-18  浏览次数:20482 次

这个如何排序?
字段一,字段二
A05,A06
A06,A07
A07,--
A10,A11
A11,A12
A12,A05

>>> sort >>
A10,A11
A11,A12
A12,A05
A05,A06
A06,A07
A07,--

请教了!

------解决方案--------------------
什么规律?
------解决方案--------------------
只能做到这一步....
SQL code
-->生成测试数据
 
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
*/

------解决方案--------------------
SQL code
create table tb(a varchar(50),b varchar(50))
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'
go

declare @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