--想来想去,还是找不到更好的,就写了这个函数出来--查询函数 create function f_qry( @FromUserID int, @ToUserID int )returns varchar(8000) as begin declare @r varchar(8000),@fid varchar(20),@tid varchar(20) select @fid=cast(@FromUserID as varchar) ,@tid=cast(@ToUserID as varchar) select @r=@fid+'->'+@tid from t_A where FromUserID=@FromUserID and ToUserID=@ToUserID if @@rowcount=0 if exists(select 1 from t_a where ToUserID=@ToUserID) and exists(select 1 from t_a where FromUserID=@FromUserID) begin declare @t table(r varchar(8000),id int,level int) declare @level int set @level=0 insert into @t(r,id,level) select @fid+'->'+cast(ToUserID as varchar) ,ToUserID,@level from t_a where FromUserID=@FromUserID select @r=a.r+'->'+@tid from @t a join t_a b on a.id=b.FromUserID where b.ToUserID=@ToUserID
while @@rowcount=0 begin set @level=@level+1 insert into @t(r,id,level) select a.r+'->'+cast(b.ToUserID as varchar) ,b.ToUserID,@level from @t a join t_a b on a.id=b.FromUserID where a.level=@level-1 if @@rowcount=0 goto lb_return select @r=a.r+'->'+@tid from @t a join t_a b on a.id=b.FromUserID where a.level=@level and b.ToUserID=@ToUserID end endlb_return: return(@r) end go
--示例--测试数据 create table t_A(FromUserID int,ToUserID int) insert t_a select 1,2 union all select 2,3 union all select 3,4 union all select 1,3 union all select 3,5 union all select 1,5 union all select 3,2 union all select 2,5 union all select 3,7 union all select 7,9 union all select 9,11 union all select 5,11 union all select 1,11 go--查询函数 create function f_qry( @FromUserID int, @ToUserID int )returns varchar(8000) as begin declare @r varchar(8000),@fid varchar(20),@tid varchar(20) select @fid=cast(@FromUserID as varchar) ,@tid=cast(@ToUserID as varchar) select @r=@fid+'->'+@tid from t_A where FromUserID=@FromUserID and ToUserID=@ToUserID if @@rowcount=0 if exists(select 1 from t_a where ToUserID=@ToUserID) and exists(select 1 from t_a where FromUserID=@FromUserID) begin declare @t table(r varchar(8000),id int,level int) declare @level int set @level=0 insert into @t(r,id,level) select @fid+'->'+cast(ToUserID as varchar) ,ToUserID,@level from t_a where FromUserID=@FromUserID select @r=a.r+'->'+@tid from @t a join t_a b on a.id=b.FromUserID where b.ToUserID=@ToUserID
while @@rowcount=0 begin set @level=@level+1 insert into @t(r,id,level) select a.r+'->'+cast(b.ToUserID as varchar) ,b.ToUserID,@level from @t a join t_a b on a.id=b.FromUserID where a.level=@level-1 if @@rowcount=0 goto lb_return select @r=a.r+'->'+@tid from @t a join t_a b on a.id=b.FromUserID where a.level=@level and b.ToUserID=@ToUserID end endlb_return: return(@r) end go--调用 select dbo.f_qry(1,5),dbo.f_qry(3,4) ,dbo.f_qry(1,7),dbo.f_qry(1,9),dbo.f_qry(1,11) go--删除测试环境 drop table t_a drop function f_qry/*--测试结果 ---------- ---------- ---------- ---------- ---------- 1->5 3->4 1->3->7 1->3->7->9 1->11(所影响的行数为 1 行) --*/
记录多肯定就速度慢.用什么方法跟记录本身没关系.这个问题关键是表记录存放方式问,记录存放时,
表明朋友关系,一定要有序,如:FID > TID ,表不要重复:
比如1 跟 2 是朋友:
存放是 FID=1 TID=2 不要倒过来,不要重复,这样查找就快了.方法只得一个个去找,只要找到就结束,而不是穷举后再找最直接的关系.
create function f_qry(
@FromUserID int,
@ToUserID int
)returns varchar(8000)
as
begin
declare @r varchar(8000),@fid varchar(20),@tid varchar(20)
select @fid=cast(@FromUserID as varchar)
,@tid=cast(@ToUserID as varchar) select @r=@fid+'->'+@tid
from t_A
where FromUserID=@FromUserID and ToUserID=@ToUserID
if @@rowcount=0
if exists(select 1 from t_a where ToUserID=@ToUserID)
and exists(select 1 from t_a where FromUserID=@FromUserID)
begin
declare @t table(r varchar(8000),id int,level int)
declare @level int set @level=0
insert into @t(r,id,level)
select @fid+'->'+cast(ToUserID as varchar)
,ToUserID,@level
from t_a where FromUserID=@FromUserID select @r=a.r+'->'+@tid
from @t a join t_a b on a.id=b.FromUserID
where b.ToUserID=@ToUserID
while @@rowcount=0
begin
set @level=@level+1
insert into @t(r,id,level)
select a.r+'->'+cast(b.ToUserID as varchar)
,b.ToUserID,@level
from @t a join t_a b on a.id=b.FromUserID
where a.level=@level-1
if @@rowcount=0 goto lb_return select @r=a.r+'->'+@tid
from @t a join t_a b on a.id=b.FromUserID
where a.level=@level and b.ToUserID=@ToUserID
end
endlb_return:
return(@r)
end
go
create table t_A(FromUserID int,ToUserID int)
insert t_a select 1,2
union all select 2,3
union all select 3,4
union all select 1,3
union all select 3,5
union all select 1,5
union all select 3,2
union all select 2,5
union all select 3,7
union all select 7,9
union all select 9,11
union all select 5,11
union all select 1,11
go--查询函数
create function f_qry(
@FromUserID int,
@ToUserID int
)returns varchar(8000)
as
begin
declare @r varchar(8000),@fid varchar(20),@tid varchar(20)
select @fid=cast(@FromUserID as varchar)
,@tid=cast(@ToUserID as varchar) select @r=@fid+'->'+@tid
from t_A
where FromUserID=@FromUserID and ToUserID=@ToUserID
if @@rowcount=0
if exists(select 1 from t_a where ToUserID=@ToUserID)
and exists(select 1 from t_a where FromUserID=@FromUserID)
begin
declare @t table(r varchar(8000),id int,level int)
declare @level int set @level=0
insert into @t(r,id,level)
select @fid+'->'+cast(ToUserID as varchar)
,ToUserID,@level
from t_a where FromUserID=@FromUserID select @r=a.r+'->'+@tid
from @t a join t_a b on a.id=b.FromUserID
where b.ToUserID=@ToUserID
while @@rowcount=0
begin
set @level=@level+1
insert into @t(r,id,level)
select a.r+'->'+cast(b.ToUserID as varchar)
,b.ToUserID,@level
from @t a join t_a b on a.id=b.FromUserID
where a.level=@level-1
if @@rowcount=0 goto lb_return select @r=a.r+'->'+@tid
from @t a join t_a b on a.id=b.FromUserID
where a.level=@level and b.ToUserID=@ToUserID
end
endlb_return:
return(@r)
end
go--调用
select dbo.f_qry(1,5),dbo.f_qry(3,4)
,dbo.f_qry(1,7),dbo.f_qry(1,9),dbo.f_qry(1,11)
go--删除测试环境
drop table t_a
drop function f_qry/*--测试结果
---------- ---------- ---------- ---------- ----------
1->5 3->4 1->3->7 1->3->7->9 1->11(所影响的行数为 1 行)
--*/
同意JSS观点,如果有主健,使用Exists判断,+一个while循环,不会慢到哪里去吧?
全当抛砖引玉了,呵呵。
[email protected]