群里朋友问题,下面解答过程如下:
要求:
该怎么样查找两个人的共同好友(这两个人还不是好友)
就像人人网实现的那样,有谁懂sql语句吗?
假如有人员表member,好友表friends
比如我和你是好友,这样你就在我的好友列表里面,现在要实现将你好友列表里面的好友推荐给我(这些被推荐的好友不能已经是我的好友)
解答1:
查询用户1,好友4,查询他们之间的共同好友。
SELECT m.uid FROM member m WHERE m.uid IN (SELECT f1.fuid FROM friends f1 LEFT JOIN friends f2 ON f1.fuid = f2.fuid WHERE f1.uid = 1 AND f2.uid = 4 )
通过not in 反过来查询他们不是共同的好友
SELECT * FROM friends f WHERE f.uid = 4 AND f.fuid NOT IN ( SELECT m.uid FROM member m WHERE m.uid IN (SELECT f1.fuid FROM friends f1 LEFT JOIN friends f2 ON f1.fuid = f2.fuid WHERE f1.uid = 1 AND f2.uid = 4 ) )
解答2:
通过使用分组查询
SELECT * FROM member m WHERE m.uid IN (SELECT f1.fuid FROM friends f1 LEFT JOIN friends f2 ON f1.fuid = f2.fuid WHERE f1.uid = 1 AND f2.uid = 4 )
SELECT * FROM member m WHERE m.uid IN (SELECT fuid FROM (SELECT *,COUNT(*) c FROM friends f WHERE f.uid IN (1,4) GROUP BY f.fuid ) f1 WHERE f1.c = 1)
解答3:
优化分组,通过having 分组过滤
SELECT *,COUNT(*) c FROM friends f WHERE f.uid IN (1,4) GROUP BY f.fuid HAVING c = 1