有两张表, 表A(start ,end),表B(p1,p2)
A表的数据结构和数据
start end a1 c1
b1 c2
d2 b3 B表的数据结构和数据
p1 p2 c1 c2
b3 b1 怎样达到这个结果 a1-c1-c2-b1-b3-d2 比如
在表A中,当条件是为a1时,我要找到所有相关的点,比如a1对应的是c1,而c1对应的是c2,c2又对应是是b1
直到最后一点d2 两张表d2都没有对应的,查询才结束。此时得到一条链路a1-c1-c2-b1-b3-d2 谢谢..
A表的数据结构和数据
start end a1 c1
b1 c2
d2 b3 B表的数据结构和数据
p1 p2 c1 c2
b3 b1 怎样达到这个结果 a1-c1-c2-b1-b3-d2 比如
在表A中,当条件是为a1时,我要找到所有相关的点,比如a1对应的是c1,而c1对应的是c2,c2又对应是是b1
直到最后一点d2 两张表d2都没有对应的,查询才结束。此时得到一条链路a1-c1-c2-b1-b3-d2 谢谢..
A表的数据结构和数据
start end a1 c1
b1 c2
d2 b3
c1 a1
c2 b1
b3 d2B表的数据结构和数据
p1 p2 c1 c2
b3 b1
c2 c1
b1 b3怎样达到这个结果 a1-c1-c2-b1-b3-d2 ------
这样感觉好多了,是吧!但是就是数据太冗余了
有两张表, 表A(start ,end),表B(p1,p2)
A表的数据结构和数据
start end a1 c1
b1 c2
d2 b3
c1 a1
c2 b1
b3 d2B表的数据结构和数据
p1 p2 c1 c2
b3 b1
c2 c1
b1 b3怎样达到这个结果
a1-c1-c2-b1-b3-d2 ------
这样感觉好多了,是吧!但是就是数据太冗余了
--测试数据
declare @t table (start varchar(10),[end] varchar(10))
insert @t
select 'a1','c1' union
select 'b1','c2' union
select 'd2','b3'
declare @t1 table (p1 varchar(10),p2 varchar(10))
insert @t1
select 'c1','c2' union
select 'b3','b1' --循环
declare @start varchar(10),@end varchar(10),@str varchar(1000)
select @str='a1',@start='a1'
while @@rowcount<>0
begin
select @end=case when start=@start then [end] else start end,
@str=@str+'-'+case when start=@start then [end] else start end
from @t where start=@start or [end]=@start
select @start=case when p1=@end then p2 else p1 end ,
@str=@str+'-'+case when p1=@end then p2 else p1 end
from @t1 where p1=@end or p2=@end
end
print @str
/*
a1-c1-c2-b1-b3-d2
*/
go
create table [ta]([start] varchar(2),[end] varchar(2))
insert [ta]
select 'a1','c1' union all
select 'b1','c2' union all
select 'd2','b3'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([p1] varchar(2),[p2] varchar(2))
insert [tb]
select 'c1','c2' union all
select 'b3','b1'select * from [ta]
select * from [tb]select id=identity(int,1,1),* into #1 from ta
--select * from #1select * into #2
from #1 a join tb b
on (a.start=b.p1 or a.start=b.p2) or (a.[end]=b.p1 or a.[end]=b.p2)
--select * from #2declare @s nvarchar(4000)
select @s=isnull(@s+'-','')+start+'-'+[end]
from
(
select distinct id
,start=case when exists(select 1 from #2 where id<a.id and (p1=a.[end] or p2=a.[end])) then a.[end] else a.start end
,[end]=case when exists(select 1 from #2 where id<a.id and (p1=a.[end] or p2=a.[end])) then a.start else a.[end] end
from #2 a
) tselect @s
--测试结果:
/*
a1-c1-c2-b1-b3-d2
*/
drop table #1
drop table #2
相同的帖子,在oracle版块里一条sql就给写出来了,
看来oracle在SQL表达功能上还是比较强大啊。
谢谢 楼上的回答,
我想你的代码有一定的局限性.比如一:
表A(start ,end),表B(p1,p2)
A表的数据结构和数据
start end b1 c2
d2 b3 B表的数据结构和数据
p1 p2 c1 c2
b3 b1 根据你的算法得到是b1-c2-b3-d2 而不是正确答案 b1-c2-c1又比如二:
A表的数据结构和数据
start end
a1 c1
b1 c2
d2 b3
d1 d2B表的数据结构和数据
p1 p2 c1 c2
b3 b1 根据你的算法得到是a1-c1-c2-b1-b3-d2 而不是正确答案 a1-c1-c2-b1-b3-d2-d1
"
select distinct id
,start=case when exists(select 1 from #2 where id<a.id and (p1=a.[end] or p2=a.[end])) then a.[end] else a.start end
,[end]=case when exists(select 1 from #2 where id<a.id and (p1=a.[end] or p2=a.[end])) then a.start else a.[end] end
from #2 a "
这里算法不适合我这里的需求.您认为呢???总之还是谢谢你
或许在我自己13楼中的举例 比如一
正确答案 b1-c2-c1 更正确的答案应该是 [d2-b3-]b1-c2-c1
谢谢您的回复,不过您和10 楼 szx1999也是同样有局限性,换一个数据就错误了.
declare @t table (start varchar(10),[end] varchar(10))
insert @t
select 'a1','c1' union
select 'b1','c2' union
select 'd2','b3' union
select 'd1','d2'
declare @t1 table (p1 varchar(10),p2 varchar(10))
insert @t1
select 'c1','c2' union
select 'b3','b1'
--查询
select * into #t from @t union select * from @t1declare @start varchar(10),@str varchar(1000),@end varchar(10)
select @start='a1',@str=@start
select @end=[end] from #twhile exists(select 1 from #t where (start=@start and [end]<>@end) or ([end]=@start and start<>@end))select @end=@start,
@str=@str+'-'+case when start=@start then [end] else start end ,
@start=case when start=@start then [end] else start end
from #t where (start=@start and [end]<>@end) or ([end]=@start and start<>@end)print @strdrop table #t
-- a1-c1-c2-b1-b3-d2-d1
但你在用如下
declare @t table (start varchar(10),[end] varchar(10))
insert @t
select 'b1','c2' union
select 'd2','b3'
declare @t1 table (p1 varchar(10),p2 varchar(10))
insert @t1
select 'c1','c2' union
select 'b3','b1'
--查询
select * into #t from @t union select * from @t1declare @start varchar(10),@str varchar(1000),@end varchar(10)
select @start='a1',@str=@start
select @end=[end] from #twhile exists(select 1 from #t where (start=@start and [end]<>@end) or ([end]=@start and start<>@end))select @end=@start,
@str=@str+'-'+case when start=@start then [end] else start end ,
@start=case when start=@start then [end] else start end
from #t where (start=@start and [end]<>@end) or ([end]=@start and start<>@end)print @strdrop table #t
-- a1得到结果为a1
不是所要的结果
go
create table [ta]([start] varchar(2),[end] varchar(2))
insert [ta]
--select 'a1','c1' union all
select 'b1','c2' union all
select 'd2','b3'
--union all select 'd1','d2'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([p1] varchar(2),[p2] varchar(2))
insert [tb]
select 'c1','c2' union all
select 'b3','b1'
gocreate function fn_coalesce(@s varchar(2),@r varchar(2))
returns nvarchar(4000)
as
begin
declare @str nvarchar(4000)
declare @t table(id int identity(1,1),s varchar(2)) insert @t select @s union all select @r while 1=1
begin
insert @t
select distinct case when p1=@r then p2 when p2=@r then p1 end
from tb join @t on p1<>s and p2<>s
where (p1=@r or p2=@r)
union all
select case @r when start then [end] else start end
from ta
where (start=@r or [end]=@r) and case @r when start then [end] else start end not in (select s from @t)
if @@rowcount=0 break
select top 1 @r=s from @t order by id desc insert @t
select distinct case when start=@r then [end] else start end
from ta join @t on start<>s and [end]<>s
where (start=@r or [end]=@r) and case when start=@r then [end] else start end not in (select s from @t) if @@rowcount=0 break
select top 1 @r=s from @t order by id desc
end
select @str=isnull(@str+'-','')+s from @t order by id
return @str
end
go
--使用函数:
select top 1 dbo.fn_coalesce(start,[end]) from ta
/*
b1-c2-c1
*/
select top 1 dbo.fn_coalesce(start,[end]) from ta order by len(dbo.fn_coalesce(start,[end])) desc
/*
d2-b3-b1-c2-c1
*/
不要忘给我"发送私信"
谢谢szx1999 ,你的多次回复,我想这达到了我的要求了.