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

周公 2011-03-16 06:52:38
有如下一个表:

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推荐给该用户。
我自己写了一个,感觉效率不是太高,现在向各位高手请教一下。
...全文
413 12 打赏 收藏 举报
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
周公 2011-03-17
分已结,谢谢大家,虽然有些朋友的答案并不能得到结果,但是仍然给分以示感谢。
  • 打赏
  • 举报
回复
haitao 2011-03-16
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
  • 打赏
  • 举报
回复
ljking0731 2011-03-16

--建表
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 行)
  • 打赏
  • 举报
回复
--小F-- 2011-03-16
select
UserId,count(1)
from
tb_UserFriend a,FriendId b
where
a.FriendId=b.FriendId
and
UserId=@pid
group by
UserId
  • 打赏
  • 举报
回复
Shawn 2011-03-16
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加索引
  • 打赏
  • 举报
回复
叶子 2011-03-16

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
*/
  • 打赏
  • 举报
回复
快溜 2011-03-16
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
  • 打赏
  • 举报
回复
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
  • 打赏
  • 举报
回复
select count(*),t.userid from tb_UserFriend s ,tb_UserFriend t
where s.friendid=t.friendid
group by t.userid
  • 打赏
  • 举报
回复
dawugui 2011-03-16
如果每个用户都要计算,则需要使用游标来做,这样的话,不如用程序去处理了.
  • 打赏
  • 举报
回复
周公 2011-03-16
[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 2011-03-16
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?


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

  • 打赏
  • 举报
回复
发帖
疑难问题
加入

2.1w+

社区成员

MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
申请成为版主
帖子事件
创建了帖子
2011-03-16 06:52
社区公告
暂无公告