如何高效查询SNS中两个人的共同好友数？

[周公]

有如下一个表：

CREATE TABLE [dbo].[tb_UserFriend](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NOT NULL,--用户ID
	[FriendId] [int] NOT NULL--好友ID
)
GO

现在有这么一个功能，要求根据给定的@UserID，按照与此@UserID的共同好友的多少排序？就是相当于将表中与该@UserID有共同好友的UserID推荐给该用户。
我自己写了一个，感觉效率不是太高，现在向各位高手请教一下。

[dawugui]

最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

[周公]

[Quote=引用 1 楼 dawugui 的回复:]
SQL code
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?



发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
[/Quote]
表结构上面提供了。
测试数据：

Insert into [tb_UserFriend]([UserId],[FriendId])values(1,3);
Insert into [tb_UserFriend]([UserId],[FriendId])values(1,4);
Insert into [tb_UserFriend]([UserId],[FriendId])values(1,5);
Insert into [tb_UserFriend]([UserId],[FriendId])values(1,6);
Insert into [tb_UserFriend]([UserId],[FriendId])values(2,4);
Insert into [tb_UserFriend]([UserId],[FriendId])values(2,5);
Insert into [tb_UserFriend]([UserId],[FriendId])values(2,6);
Insert into [tb_UserFriend]([UserId],[FriendId])values(3,5);
Insert into [tb_UserFriend]([UserId],[FriendId])values(3,6);

对于编号为2的用户，可以看出1与2之间有共同的好友个数为3（4、5、6），2与3个共同好友个数为2（5、6），那么结果应该是：
------------------------------
count UserId
3 1
2 3

[dawugui]

如果每个用户都要计算,则需要使用游标来做,这样的话,不如用程序去处理了.

[让你望见影子的墙]

select count(*),t.userid from tb_UserFriend s ,tb_UserFriend t
where s.friendid=t.friendid 
group by t.userid

[让你望见影子的墙]

if object_id('p1') is not null
   drop proc p1
go
create proc p1
@userid int
as
begin
select *
from(
  select cnt=count(*),t.userid from tb_UserFriend s ,tb_UserFriend t
  where s.friendid=t.friendid and s.userid=@userid
  group by t.userid)K
where userid!=@userid
end

测试：
exec p1 2

结果：
cnt         userid
----------- -----------
3           1
2           3

[快溜]

CREATE TABLE [dbo].[tb_UserFriend](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,--用户ID
    [FriendId] [int] NOT NULL--好友ID
)
GO
Insert into [tb_UserFriend]([UserId],[FriendId])values(1,3);
Insert into [tb_UserFriend]([UserId],[FriendId])values(1,4);
Insert into [tb_UserFriend]([UserId],[FriendId])values(1,5);
Insert into [tb_UserFriend]([UserId],[FriendId])values(1,6);
Insert into [tb_UserFriend]([UserId],[FriendId])values(2,4);
Insert into [tb_UserFriend]([UserId],[FriendId])values(2,5);
Insert into [tb_UserFriend]([UserId],[FriendId])values(2,6);
Insert into [tb_UserFriend]([UserId],[FriendId])values(3,5);
Insert into [tb_UserFriend]([UserId],[FriendId])values(3,6);

select UserId,count(1)
from tb_UserFriend a,(select FriendId from tb_UserFriend where UserId=2) b
where a.FriendId=b.FriendId
group by UserId

/*
UserId      
----------- -----------
1           3
2           3
3           2

[叶子]

declare @pid int
set @pid =2  --变量

select count(1) as [count],UserId
from tb_UserFriend a,(select FriendId from tb_UserFriend where UserId=@pid) b
where a.FriendId=b.FriendId and a.UserId<>@pid
group by UserId
/*
count       UserId
----------- -----------
3           1
2           3
*/

[Shawn]

CREATE TABLE #temp(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,--用户ID
    [FriendId] [int] NOT NULL--好友ID
)
GO
Insert into #temp([UserId],[FriendId])values(1,3);
Insert into #temp([UserId],[FriendId])values(1,4);
Insert into #temp([UserId],[FriendId])values(1,5);
Insert into #temp([UserId],[FriendId])values(1,6);
Insert into #temp([UserId],[FriendId])values(2,4);
Insert into #temp([UserId],[FriendId])values(2,5);
Insert into #temp([UserId],[FriendId])values(2,6);
Insert into #temp([UserId],[FriendId])values(3,5);
Insert into #temp([UserId],[FriendId])values(3,6);
go
--SQL:
declare @UserId varchar(10)
set @UserId = 2

select cnt=COUNT(1), b.UserId
from #temp a inner join #temp b
on a.FriendId = b.FriendId and a.UserId = @UserId and a.UserId <> b.UserId
group by b.UserId
/*
cnt	UserId
3	1
2	3
*/
--FriendId加索引
--UserID加索引

[--小F--]

select
 UserId,count(1)
from
 tb_UserFriend a,FriendId  b
where
 a.FriendId=b.FriendId
and
 UserId=@pid
group by
 UserId

[ljking0731]

--建表
CREATE TABLE #tb_UserFriend(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,--用户ID
    [FriendId] [int] NOT NULL--好友ID
)
--数据
Insert into [#tb_UserFriend]([UserId],[FriendId])values(1,3)
Insert into [#tb_UserFriend]([UserId],[FriendId])values(1,4)
Insert into [#tb_UserFriend]([UserId],[FriendId])values(1,5)
Insert into [#tb_UserFriend]([UserId],[FriendId])values(1,6)
Insert into [#tb_UserFriend]([UserId],[FriendId])values(2,4)
Insert into [#tb_UserFriend]([UserId],[FriendId])values(2,5)
Insert into [#tb_UserFriend]([UserId],[FriendId])values(2,6)
Insert into [#tb_UserFriend]([UserId],[FriendId])values(3,5)
Insert into [#tb_UserFriend]([UserId],[FriendId])values(3,6)


declare @UserID int
--当@UserID=2时
set @UserID=2
--结果
select count(0) as [count],b.UserID from #tb_UserFriend as a,#tb_UserFriend as b
where a.friendID=b.friendID
and a.UserID=@UserID and b.UserID<>@UserID
group by b.UserID

--
count       UserID      
----------- ----------- 
3           1
2           3

（所影响的行数为 2 行）

[haitao]

select distinct userid
from tb_UserFriend
where Friend in (
	select Friend
	from tb_UserFriend
	where Userid=@userid
) and userid<>@userid

in的效率不会高
有索引的话，join会快很多

select distinct b.userid
from (
	select Friend
	from tb_UserFriend
	where Userid=@userid
) a
left join tb_UserFriend b
on a.friend=b.friend
where b.userid<>@userid

[周公]

分已结，谢谢大家，虽然有些朋友的答案并不能得到结果，但是仍然给分以示感谢。