（高难）sql 多级递归求高效算法

算法描述：Mid(我的id)，Fid(朋友id)；如1是我的id那表示我有两个好友2和7，此为第一级人际关系，我的好友2有两个好友3和8，此为第二级人际关系，3有一个好友6，此为第三级人际关系，现在我任意输入一个id号，判断其是否在我的前三级人际关系里面，注意相互加为好友的情况


Mid Fid
1 2
2 3
3 6
1 7
2 8
6 9
7 10
2 1
6 3
… …

BOM递归查询。

SQL code

create table tb(Mid int,Fid int)
insert into tb select 1,2
insert into tb select 2,3
insert into tb select 3,6
insert into tb select 1,7
insert into tb select 2,8
insert into tb select 6,9
insert into tb select 7,10
insert into tb select 2,1
insert into tb select 6,3
go
declare @a int,@b int
set @a=1
set @b=10
select 'yes' from tb a where Mid=@a and exists(select 1 from tb b where Mid=a.Fid and exists(select 1 from tb where Mid=a.Fid and Fid=@b))
/*
----
yes

(1 行受影响)
*/
set @b=9
select 'yes' from tb a where Mid=@a and exists(select 1 from tb b where Mid=a.Fid and exists(select 1 from tb where Mid=a.Fid and Fid=@b))
/*
----

(0 行受影响)

*/
go
drop table tb

SQL code

create table tb(Mid int,Fid int)
insert into tb select 1,2
insert into tb select 2,3
insert into tb select 3,6
insert into tb select 1,7
insert into tb select 2,8
insert into tb select 6,9
insert into tb select 7,10
insert into tb select 2,1
insert into tb select 6,3
go
create function getfriendinthreeclass
(@a int,@b int)
returns varchar(3)
as
begin
declare @f varchar(3)
if exists(select 1 from tb a where Mid=@a and exists(select 1 from tb b where Mid=a.Fid and exists(select 1 from tb where Mid=a.Fid and Fid=@b)))
set @f='Yes'
else
set @f='No'
return @f
end
go
select dbo.getfriendinthreeclass(1,10)
/*
----
yes

(1 行受影响)
*/
select dbo.getfriendinthreeclass(1,9)
/*
----
No
(1 行受影响)

*/
go
drop table tb
drop function dbo.getfriendinthreeclass

修正:
SQL code

create table tb(Mid int,Fid int)
insert into tb select 1,2
insert into tb select 2,3
insert into tb select 3,6
insert into tb select 1,7
insert into tb select 2,8
insert into tb select 6,9
insert into tb select 7,10
insert into tb select 2,1
insert into tb select 6,3
go
create function getfriendinthreeclass
(@a int,@b int)
returns varchar(3)
as
begin
declare @f varchar(3)
;with cte as(
select *,1 flg from tb where mid=@a
union all
select a.*,b.flg+1 from tb a inner join cte b on a.mid=b.fid and b.flg<=3
)select @f=(case when exists(select 1 from cte where fid=@b and flg<3) then 'Yes' else 'No' end)
return @f
end
go
select dbo.getfriendinthreeclass(1,10)
/*
----
yes

(1 行受影响)
*/
select dbo.getfriendinthreeclass(1,2)
/*
----
Yes

(1 行受影响)

*/
select dbo.getfriendinthreeclass(1,6)
/*
----
No
(1 行受影响)

*/
go
drop table tb
drop function dbo.getfriendinthreeclass

去精华贴里找吧