我有一个表,其中每行都有一个 From 和一个 To:
From|To
A | B
C | D
B | C
D | E
我需要按顺序对它们进行排序,其中第一行中的"To"是以下内容的"发件人",依此类推。
结果应按如下顺序排序:
From|To
A| B
B| C
C| D
D| E
主要问题是在上一条记录中查找 To 后面的 From 。
不确定我是否正确回答了您的问题。你为什么不直接排序?
;with cte as
(
select 'B' as "From", 'C' as "To"
union
select 'A', 'B'
union
select 'C', 'D'
)
select "From", "To"
from cte
order by "From";
(查询中不需要 cte/union 内容,这只是为了示例数据。
如果只想列出具有后继条目的条目:
;with cte as
(
select 'B' as "From", 'C' as "To"
union
select 'A', 'B'
union
select 'C', 'D'
union
select 'F', 'G'
)
select "From", "To"
from cte
where exists(select *
from cte cte2
where cte2."From" = cte."To")
or exists(select *
from cte cte3
where cte3."To" = cte."From")
order by "From";
顺便说一句:尝试使用除"From"和"To"以外的其他列名,因为它们是保留语句,必须与"(ANSI(或[](T SQL(一起使用。
declare @v table (ffrom varchar(10), fto varchar(10)); insert into @v values ('e', 'f'), ('a', 'b'), ('g', 'h'), ('c', 'd'); select * from @v order by ffrom;
去
FFROM |金融贸易协定:---- |:--一 |b c |d e |f 克 |h
在这里小提琴
您可以使用递归 CTE:
with cte as (
select from, to, 1 as lev
from t
where not exists (select 1 from t t2 where t2.to = t.from)
union all
select t.from, t.to, cte.lev + 1
from cte join
t
on cte.to = t.from
)
select to, from
from cte
order by lev, to;
请注意,这受最大递归深度的影响,但它在四行上可以正常工作。