You need to enable JavaScript to run this app. 如何通过SQL查询与指定用户拥有最多共同好友的用户
优惠活动
大模型
产品
解决方案
定价
更多

如何通过SQL查询与指定用户拥有最多共同好友的用户

实现方案

现有好友关系表RELATION,包含user1、user2两个字段,两个用户为好友时表中仅存储一条无顺序的关联记录,无重复数据。要求查询与用户A共同好友数量最多的用户。

实现思路

  • 先提取用户A的全部好友集合,兼容A出现在user1或user2两种场景
  • 生成全量双向好友映射表,把原本单向的存储记录转为双向映射,方便匹配任意用户的全部好友
  • 将其他用户的好友列表和A的好友列表关联,统计共同好友数量,倒序排序后取最高值即可

基础查询SQL(仅取排名第一的用户)

SELECT ar.u AS target_user, COUNT(*) AS common_friend_num
FROM 
-- 生成双向好友映射
(SELECT user1 AS u, user2 AS friend FROM RELATION UNION ALL SELECT user2 AS u, user1 AS friend FROM RELATION) ar
INNER JOIN
-- 提取用户A的全部好友
(SELECT IF(user1 = 'A', user2, user1) AS a_friend FROM RELATION WHERE user1 = 'A' OR user2 = 'A') af
ON ar.friend = af.a_friend
-- 排除用户A自身
WHERE ar.u != 'A'
GROUP BY ar.u
ORDER BY common_friend_num DESC
LIMIT 1;

用示例数据执行该SQL,返回结果为target_user: C, common_friend_num: 3,和预期结果一致。

拓展版本(支持获取所有并列最高的用户)

如果存在多个用户和A的共同好友数量并列最高,可以用窗口函数实现:

WITH all_relation AS (
    SELECT user1 AS u, user2 AS friend FROM RELATION
    UNION ALL
    SELECT user2 AS u, user1 AS friend FROM RELATION
),
a_friends AS (
    SELECT IF(user1 = 'A', user2, user1) AS a_friend FROM RELATION WHERE user1 = 'A' OR user2 = 'A'
),
common_rank AS (
    SELECT 
        ar.u AS target_user,
        COUNT(*) AS common_friend_num,
        RANK() OVER(ORDER BY COUNT(*) DESC) AS rk
    FROM all_relation ar
    INNER JOIN a_friends af ON ar.friend = af.a_friend
    WHERE ar.u != 'A'
    GROUP BY ar.u
)
SELECT target_user, common_friend_num FROM common_rank WHERE rk = 1;

内容的提问来源于stack exchange,提问作者devyoon

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.10 12:45:01