MySQL查询user2好友及与user1的共同好友数报错求助

嘿，我来帮你搞定这个问题！你遇到的Unknown column 'users.user_id' in 'where clause'错误，本质是MySQL子查询的作用域限制——你用来计算mutual_count的嵌套子查询（最内层的a和b子查询）没办法直接访问外层users表的user_id，因为它属于更深的嵌套层级，超出了作用范围。

咱们先理清楚核心需求：先找出user2的所有好友，再逐个统计每个好友和user1的共同好友数量。下面给你两种可行的修正方案：

方案1：使用CTE（MySQL 8.0+ 支持，代码更清晰） #

CTE可以把临时表的逻辑拆分出来，可读性更强，也能彻底避免作用域问题：

-- 先定义user2的所有好友列表
WITH user2_friends AS (
    SELECT DISTINCT 
        CASE 
            WHEN user_one_id = 2 THEN user_two_id 
            ELSE user_one_id 
        END AS user_id
    FROM friends
    WHERE user_one_id = 2 OR user_two_id = 2
),
-- 定义user1的所有好友列表
user1_friends AS (
    SELECT DISTINCT 
        CASE 
            WHEN user_one_id = 1 THEN user_two_id 
            ELSE user_one_id 
        END AS friend_id
    FROM friends
    WHERE user_one_id = 1 OR user_two_id = 1
)
SELECT 
    uf.user_id,
    COUNT(uf1.friend_id) AS mutual_count
FROM user2_friends uf
LEFT JOIN (
    -- 获取每个user2好友的所有好友
    SELECT DISTINCT 
        CASE 
            WHEN user_one_id = uf_inner.user_id THEN user_two_id 
            ELSE user_one_id 
        END AS friend_id,
        uf_inner.user_id
    FROM friends
    JOIN user2_friends uf_inner 
        ON user_one_id = uf_inner.user_id OR user_two_id = uf_inner.user_id
) uf_friends ON uf.user_id = uf_friends.user_id
LEFT JOIN user1_friends uf1 ON uf_friends.friend_id = uf1.friend_id
GROUP BY uf.user_id
ORDER BY uf.user_id;

方案2：兼容老版本MySQL（不用CTE） #

如果你的MySQL版本低于8.0，不支持CTE，可以用嵌套子查询替代：

SELECT 
    uf.user_id,
    COUNT(uf1.friend_id) AS mutual_count
FROM (
    -- 获取user2的所有好友
    SELECT DISTINCT 
        CASE 
            WHEN user_one_id = 2 THEN user_two_id 
            ELSE user_one_id 
        END AS user_id
    FROM friends
    WHERE user_one_id = 2 OR user_two_id = 2
) uf
LEFT JOIN (
    -- 获取每个user2好友的所有好友
    SELECT DISTINCT 
        CASE 
            WHEN user_one_id = uf_inner.user_id THEN user_two_id 
            ELSE user_one_id 
        END AS friend_id,
        uf_inner.user_id
    FROM friends
    JOIN (
        SELECT DISTINCT 
            CASE 
                WHEN user_one_id = 2 THEN user_two_id 
                ELSE user_one_id 
            END AS user_id
        FROM friends
        WHERE user_one_id = 2 OR user_two_id = 2
    ) uf_inner 
        ON user_one_id = uf_inner.user_id OR user_two_id = uf_inner.user_id
) uf_friends ON uf.user_id = uf_friends.user_id
LEFT JOIN (
    -- 获取user1的所有好友
    SELECT DISTINCT 
        CASE 
            WHEN user_one_id = 1 THEN user_two_id 
            ELSE user_one_id 
        END AS friend_id
    FROM friends
    WHERE user_one_id = 1 OR user_two_id = 1
) uf1 ON uf_friends.friend_id = uf1.friend_id
GROUP BY uf.user_id
ORDER BY uf.user_id;

为什么原来的SQL会报错？ #

你原来的写法里，计算mutual_count的子查询是一个相关子查询，但嵌套了两层（a和b的子查询在最内层）。MySQL的相关子查询只能访问直接外层的表，更深的嵌套层级就无法访问外层的users.user_id了。通过把好友列表提前提取为临时表（CTE或子查询），后续的关联操作就能正确访问到对应的字段。

预期输出 #

执行上面的SQL后，会得到你想要的结果：

user_id | mutual_count
------------------------
1       | 2
3       | 1
4       | 1
5       | 0

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