可能描述的有些问题 我还是举例吧
表A a列 b列
北京 太原
表B a列 b列
太原 石家庄
表C a列 b列
石家庄 上海
表D a列 b列
上海 广州
如果x=北京 y=广州 利用这两个条件 把表A B C D这几条数据联系起来 并且都能找出来
我和lz是一起的
表A a列 b列
北京 太原
表B a列 b列
太原 石家庄
表C a列 b列
石家庄 上海
表D a列 b列
上海 广州
如果x=北京 y=广州 利用这两个条件 把表A B C D这几条数据联系起来 并且都能找出来
我和lz是一起的
from a
left join b on a.b = b.a
left join c on b.b = c.a
left join d on c.b = d.a
declare @A table (a varchar(20),b varchar(20),id int)
insert @A select
'北京' , '太原' ,1
union all select
'太原' , '石家庄' ,2
union all select
'石家庄', '上海' ,3
union all select
'上海' , '广州',4
union all select
'石家庄', '北京' ,5
union all select
'上海', '太原' ,6
declare @a1 varchar(20)
set @a1='北京'
declare @a2 varchar(20)
set @a2='广州'declare @t table (a varchar(20),b varchar(20),path varchar(100))
insert @t select a,b,','+cast(id as varchar)+',' as path
from @A where a=@a1while not exists (select 1 from @t where a=@a1 and b=@a2) --没找到结果
and exists (select 1 from @t t,@A a where t.b=a.a
and charindex(','+cast(a.id as varchar)+',',t.path)<=0 --走过的路不能再走
and not exists (select 1 from @t where a=t.a and b=a.b and path=t.path+cast(a.id as varchar)+',')
)
begin
select * from @t
insert @t
select t.a,a.b,t.path+cast(a.id as varchar)+',' from @t t,@A a where t.b=a.a
and charindex(','+cast(a.id as varchar)+',',t.path)<=0 --走过的路不能再走
and not exists (select 1 from @t where a=t.a and b=a.b and path=t.path+cast(a.id as varchar)+',') --拒绝重复
end
if exists (select 1 from @t where a=@a1 and b=@a2)
begin
print '找到'
select * from @t where a=@a1 and b=@a2
--可以在这里改变显示结果的方法
end
else
print '找不到'
insert @A select
'北京' , '太原' ,1
union all select
'太原' , '石家庄' ,2
union all select
'石家庄', '上海' ,3
union all select
'上海' , '广州',4
union all select
'石家庄', '北京' ,5
union all select
'上海', '太原' ,6
declare @a1 varchar(20)
set @a1='北京'
declare @a2 varchar(20)
set @a2='广州'declare @t table (a varchar(20),b varchar(20),path varchar(100))
insert @t select a,b,','+cast(id as varchar)+',' as path
from @A where a=@a1while not exists (select 1 from @t where a=@a1 and b=@a2) --没找到结果
and exists (select 1 from @t t,@A a where t.b=a.a
and charindex(','+cast(a.id as varchar)+',',t.path)<=0 --走过的路不能再走
and not exists (select 1 from @t where a=t.a and b=a.b and path=t.path+cast(a.id as varchar)+',')
)
begin
insert @t
select t.a,a.b,t.path+cast(a.id as varchar)+',' from @t t,@A a where t.b=a.a
and charindex(','+cast(a.id as varchar)+',',t.path)<=0 --走过的路不能再走
and not exists (select 1 from @t where a=t.a and b=a.b and path=t.path+cast(a.id as varchar)+',') --拒绝重复
end
if exists (select 1 from @t where a=@a1 and b=@a2)
begin
print '找到'
select * from @t where a=@a1 and b=@a2
end
else
print '找不到'
--结果
找到
a b path
-------------------- -------------------- ----------------------------------------------------------------------------------------------------
北京 广州 ,1,2,3,4,(所影响的行数为 1 行)