优化Facebook共同好友SQL查询：JOIN实现及好友推荐疑问

嘿，我来帮你把这个问题拆解清楚——先搞定用JOIN实现双向好友关系下的共同好友查询，再聊聊Facebook风格好友推荐的SQL思路，完全适配MS SQL Server哦！

你的原有查询用UNION合并两个用户的好友列表再找交集，虽然能工作，但用JOIN可以让逻辑更清晰，性能也更优（尤其是数据量大时）。核心思路是先把每个用户的好友统一整理成单列表，再用INNER JOIN求交集。

方案1：用CTE（可读性优先） #

CTE能把逻辑分层，让代码更易维护，MS SQL Server对它的支持非常好：

-- 定义CTE：获取用户A的所有好友（不管关系存在哪一列）
WITH UserAFriends AS (
    SELECT 
        CASE 
            WHEN userId = 'A' THEN friendId 
            ELSE userId 
        END AS friendId
    FROM friendsTable
    WHERE userId = 'A' OR friendId = 'A'
),
-- 定义CTE：获取用户B的所有好友
UserBFriends AS (
    SELECT 
        CASE 
            WHEN userId = 'B' THEN friendId 
            ELSE userId 
        END AS friendId
    FROM friendsTable
    WHERE userId = 'B' OR friendId = 'B'
)
-- 用INNER JOIN直接取两个好友列表的交集，排除A和B自己
SELECT uaf.friendId AS mutualFriendId
FROM UserAFriends uaf
INNER JOIN UserBFriends ubf ON uaf.friendId = ubf.friendId
WHERE uaf.friendId NOT IN ('A', 'B')

方案2：子查询直接JOIN（简洁版） #

如果不想用CTE，直接把两个好友列表作为子查询JOIN也可以：

SELECT a.friendId AS mutualFriendId
FROM (
    SELECT CASE WHEN userId = 'A' THEN friendId ELSE userId END AS friendId
    FROM friendsTable
    WHERE userId = 'A' OR friendId = 'A'
) a
INNER JOIN (
    SELECT CASE WHEN userId = 'B' THEN friendId ELSE userId END AS friendId
    FROM friendsTable
    WHERE userId = 'B' OR friendId = 'B'
) b ON a.friendId = b.friendId
WHERE a.friendId NOT IN ('A', 'B')

这两个方案都完美处理了好友关系双向存储的问题——不管A是在userId还是friendId列，都能正确提取出所有好友。

Facebook的核心好友推荐逻辑是推荐和你有最多共同好友的人，同时排除已经是好友的人和你自己。下面是适配MS SQL Server的实现：

-- 先指定要推荐的目标用户
DECLARE @TargetUserId VARCHAR(50) = 'X'

WITH TargetFriends AS (
    -- 第一步：获取目标用户的所有好友，统一成单列表
    SELECT 
        CASE 
            WHEN userId = @TargetUserId THEN friendId 
            ELSE userId 
        END AS friendId
    FROM friendsTable
    WHERE userId = @TargetUserId OR friendId = @TargetUserId
),
FriendOfFriends AS (
    -- 第二步：获取目标用户好友的所有好友（二度人脉）
    SELECT 
        CASE 
            WHEN userId = tf.friendId THEN friendId 
            ELSE userId 
        END AS potentialFriendId
    FROM friendsTable ft
    INNER JOIN TargetFriends tf ON ft.userId = tf.friendId OR ft.friendId = tf.friendId
    -- 排除目标用户自己
    WHERE CASE WHEN userId = tf.friendId THEN friendId ELSE userId END != @TargetUserId
)
-- 第三步：统计每个潜在好友的共同好友数，排除已好友，按数量降序排序
SELECT 
    fof.potentialFriendId,
    COUNT(*) AS commonFriendCount
FROM FriendOfFriends fof
-- 用LEFT JOIN排除已经是目标用户好友的人
LEFT JOIN TargetFriends tf ON fof.potentialFriendId = tf.friendId
WHERE tf.friendId IS NULL
GROUP BY fof.potentialFriendId
ORDER BY commonFriendCount DESC

逻辑说明： #

1. TargetFriends 先整理出目标用户的全部好友；
2. FriendOfFriends 拉取这些好友的所有好友，得到目标用户的二度人脉；
3. 最后统计每个潜在好友的共同好友数量，排除已好友后按数量排序——这就是Facebook最基础的好友推荐逻辑（实际还会加入资料相似度、互动频率等维度，但核心逻辑不变）。

内容的提问来源于stack exchange，提问作者needdemhelps