我已经查看了这些问题,但没有找到完全符合我需求的问题,也无法自己解决。
我有两个表,一个是用户表,一个是好友链接表。用户表是所有用户的表格:
+---------+------------+---------+---------------+
| user_id | first_name | surname | email |
+---------+------------+---------+---------------+
1 joe bloggs joe@test.com
2 bill bloggs bill@test.com
3 john bloggs john@test.com
4 karl bloggs karl@test.com
我的朋友链接表显示了用户之间所有的关联,例如:
+--------=+---------+-----------+--------+
| link_id | user_id | friend_id | status |
+---------+---------+-----------+--------+
1 1 3 a
2 3 1 a
3 4 3 a
4 3 4 a
5 2 3 a
6 3 2 a
作为说明,状态列中的a表示已批准,还可以有r(请求)和d(拒绝)。
我想要的是一个查询,如果用户进行搜索,它将返回一个用户列表,他们目前还不是朋友,并且每个用户与他们有多少共同的朋友。
我已经成功地得到了一个查询,用于获取所有当前不是他们朋友的用户。所以如果进行搜索的用户具有用户ID 1:
SELECT u.user_id,u.first_name,u.surname
FROM users u
LEFT JOIN friend_links fl
ON u.user_id = fl.user_id AND 1 IN (fl.friend_id)
WHERE fl.friend_id IS NULL
AND u.user_id != 1
AND surname LIKE 'bloggs'
那么,我如何统计每个返回用户的共同好友数量呢?
编辑:
由于我认为我的问题不是特别清楚,因此进行编辑。
我目前拥有的查询将产生以下一组结果:
+---------+------------+---------+
| user_id | first_name | surname |
+---------+------------+---------+
2 bill bloggs
4 karl bloggs
这些用户与“Bloggs”姓氏匹配,目前不是Joe Bloggs(用户ID 1)的好友。
然后我想知道这些用户中每个人与搜索者有多少共同好友,以便返回以下结果:
+---------+------------+---------+--------+
| user_id | first_name | surname | mutual |
+---------+------------+---------+--------+
2 bill bloggs 1
4 karl bloggs 1
每个返回的用户都有一个共同的好友,因为Joe Bloggs(用户ID 1)与John Bloggs是朋友,而John Bloggs又与两个返回的用户都是朋友。
希望这样更清楚了。
谢谢。
INNER JOIN
改为LEFT JOIN
。 - Sammitch$person
是朋友的用户。 - Shu