求一个SQL

[人间太皮]

CREATE TABLE aaa (
colA VARCHAR(30),
colB VARCHAR(30)
)

A B
B C
C D
D E
1 2
2 3
3 4

插入的数据首尾相接，需要通过最后一个值找到第一个值
比如：通过E找到A，通过4找到1

[人间太皮]

也可通过中间的值查找，比如通过C找A，通过3找1. 只往上查找，不考虑往下查找

[卖水果的net]

CREATE TABLE aaa (
colA VARCHAR(30),
colB VARCHAR(30)
)

insert into aaa values
('A','B'),('B','C'),('C','D'),('D','E'),
('1','2'),('2','3'),('3','4')
go
with m as (
    select 0 as id, t.colA, t.colB from aaa t where colB ='E' -- '4'
    union all
    select m.id + 1 , t.colA, t.colB from aaa t,m where m.colA = t.colB
)
select top 1 * from m order by id desc 
go
drop table aaa 
go



(7 行受影响)
id          colA                           colB
----------- ------------------------------ ------------------------------
3           A                              B

(1 行受影响)

[人间太皮]

真机智，我只想到with，没想到 加字段。。。。

[人间太皮]

引用 2 楼 wmxcn2000 的回复:

CREATE TABLE aaa (
colA VARCHAR(30),
colB VARCHAR(30)
)

insert into aaa values
('A','B'),('B','C'),('C','D'),('D','E'),
('1','2'),('2','3'),('3','4')
go
with m as (
    select 0 as id, t.colA, t.colB from aaa t where colB ='E' -- '4'
    union all
    select m.id + 1 , t.colA, t.colB from aaa t,m where m.colA = t.colB
)
select top 1 * from m order by id desc 
go
drop table aaa 
go



(7 行受影响)
id          colA                           colB
----------- ------------------------------ ------------------------------
3           A                              B

(1 行受影响)

不过还有个问题，这里只能找单个，如果E 和4 同时要找呢

[gw6328]

引用 4 楼 ai_quaner 的回复:

[quote=引用 2 楼 wmxcn2000 的回复:]

CREATE TABLE aaa (
colA VARCHAR(30),
colB VARCHAR(30)
)

insert into aaa values
('A','B'),('B','C'),('C','D'),('D','E'),
('1','2'),('2','3'),('3','4')
go
with m as (
    select 0 as id, t.colA, t.colB from aaa t where colB ='E' -- '4'
    union all
    select m.id + 1 , t.colA, t.colB from aaa t,m where m.colA = t.colB
)
select top 1 * from m order by id desc 
go
drop table aaa 
go



(7 行受影响)
id          colA                           colB
----------- ------------------------------ ------------------------------
3           A                              B

(1 行受影响)

不过还有个问题，这里只能找单个，如果E 和4 同时要找呢[/quote] 把条件改一下，但是你只能找到最上面两个，你不能确定哪个对应哪个。

[卖水果的net]

CREATE TABLE aaa (
colA VARCHAR(30),
colB VARCHAR(30)
)

insert into aaa values
('A','B'),('B','C'),('C','D'),('D','E'),
('1','2'),('2','3'),('3','4')
go
with m as (
    select 0 as id, t.colA, t.colB, t.colB leaf_colB 
    from aaa t where colB  in('E','4')
    union all
    select m.id + 1 , t.colA, t.colB, m.leaf_colB 
    from aaa t,m where m.colA = t.colB
)
select * from m 
where not exists(select * from m a where m.leaf_colB = leaf_colB and id> m.id)
go
drop table aaa 
go


(7 行受影响)
id          colA                 colB                 leaf_colB
----------- -------------------- -------------------- ----------
2           1                    2                    4
3           A                    B                    E

(2 行受影响)