如何在同一查询中获取指定用户的好友列表及各好友的好友数

我来帮你搞定这个需求！你需要在查询特定用户（比如示例中的ID 33）的好友列表时，同时算出每个好友的总好友数量，对吧？结合你给出的表结构，我整理了一个精准适配的SQL方案：

完整查询语句 #

WITH user_friend_counts AS (
    SELECT 
        user_id,
        COUNT(*) AS friend_count
    FROM (
        -- 展开所有双向好友关系，确保每个用户的每段好友关系都被统计
        SELECT user_id FROM friends
        UNION ALL
        SELECT user_id2 FROM friends
    ) AS all_friends
    GROUP BY user_id
)
SELECT 
    u.*,
    COALESCE(ufc.friend_count, 0) AS friend_count
FROM users u
INNER JOIN friends f 
    ON (f.user_id = u.id AND f.user_id2 = 33) 
    OR (f.user_id2 = u.id AND f.user_id = 33)
LEFT JOIN user_friend_counts ufc 
    ON u.id = ufc.user_id
WHERE u.id != 33
GROUP BY u.id, ufc.friend_count;

逻辑说明 #

• CTE统计好友数：通过UNION ALL把friends表中所有用户的双向好友关系展开，再按用户ID分组计数，得到每个用户的总好友数。这里用UNION ALL而不是UNION，是为了完整保留所有关系（哪怕存在重复好友记录的情况）。
• 主查询关联好友列表：修正了原查询的JOIN逻辑，更明确地匹配目标用户（ID 33）的好友关系，避免逻辑歧义。
• 处理无好友的情况：用COALESCE把没有好友的用户的计数设为0，避免返回NULL值。
• 去重保证唯一性：通过GROUP BY确保每个好友只出现在结果中一次，和你原查询的逻辑保持一致。

测试示例 #

比如用你提供的测试数据，查询用户20的好友列表时，把SQL中的33替换为20，会得到如下结果：

这个结果完全符合预期：用户21是用户20的好友，且用户21的好友数为1。

内容的提问来源于stack exchange，提问作者Abdellah Stands with Gaza