如何使用MySQL查询好友列表及各好友对应的共同好友数量

前提约定 #

统一两张表的命名：

• 用户表：users，包含字段user_id、name
• 好友关系表：friend_relations，包含字段user_id、friend_id

实现SQL #

以下是可直接运行的查询语句，输入参数为当前用户ID，示例中为1，可根据需求替换：

SELECT 
    u.user_id,
    u.name,
    COUNT(fr2.friend_id) AS mutual_friend_count
FROM 
    friend_relations fr1
    JOIN users u ON fr1.friend_id = u.user_id
    LEFT JOIN friend_relations fr2 
        ON fr2.user_id = fr1.friend_id
        -- 匹配当前用户的好友列表，找共同好友
        AND fr2.friend_id IN (SELECT friend_id FROM friend_relations WHERE user_id = 1)
        -- 排除当前用户自身，避免计入共同好友
        AND fr2.friend_id != 1
WHERE 
    -- 过滤当前用户的所有好友
    fr1.user_id = 1
GROUP BY 
    u.user_id, u.name;

逻辑说明 #

1. 首先通过fr1.user_id = 1过滤出当前用户的全部好友，关联users表获取好友的基础信息
2. 关联好友关系表fr2拿到每个好友的好友列表
3. 将每个好友的好友ID和当前用户的好友ID做匹配，匹配成功的即为共同好友
4. 排除当前用户自身ID，避免把自己误算为共同好友
5. 分组统计每个好友的匹配条数，即为你和该好友的共同好友数量

运行结果 #

基于提供的示例数据，运行上述SQL得到的输出如下：

完全符合预期输出要求。

通用优化 #

如果需要适配不同的输入用户，可提前定义变量替换硬编码的用户ID：

-- 定义当前用户ID变量，可动态修改
SET @current_user_id = 1;

SELECT 
    u.user_id,
    u.name,
    COUNT(fr2.friend_id) AS mutual_friend_count
FROM 
    friend_relations fr1
    JOIN users u ON fr1.friend_id = u.user_id
    LEFT JOIN friend_relations fr2 
        ON fr2.user_id = fr1.friend_id
        AND fr2.friend_id IN (SELECT friend_id FROM friend_relations WHERE user_id = @current_user_id)
        AND fr2.friend_id != @current_user_id
WHERE 
    fr1.user_id = @current_user_id
GROUP BY 
    u.user_id, u.name;

内容的提问来源于stack exchange，提问作者Voon Tao Tan