MySQL嵌套子查询关联主表报错：未知users.user_id字段求助

首先，你的报错核心原因是MySQL的子查询作用域限制：最深层的子查询（也就是你用来获取好友ID的UNION子查询）无法直接引用最外层的users.user_id——MySQL的作用域规则只允许子查询访问直接外层的字段，跨多层级的字段引用是不被支持的。

下面是两种可行的解决方案，都能精准实现你的需求：

方案一：使用CTE（MySQL 8.0+ 推荐） #

CTE可以把「获取用户好友列表」的逻辑抽离出来，代码可读性和复用性更强：

WITH user_friends AS (
    -- 生成双向的好友关系，避免重复统计
    SELECT 
        user_one_id AS user_id, 
        user_two_id AS friend_id
    FROM friends
    UNION
    SELECT 
        user_two_id AS user_id, 
        user_one_id AS friend_id
    FROM friends
),
-- 预获取user1的所有好友集合
user1_friends AS (
    SELECT friend_id FROM user_friends WHERE user_id = 1
),
-- 预获取user2的所有好友集合
user2_friends AS (
    SELECT friend_id FROM user_friends WHERE user_id = 2
)
-- 统计user2每个好友与user1的共同好友数
SELECT 
    uf.friend_id AS user_id,
    COUNT(u1.friend_id) AS mutual_count
FROM user2_friends uf
LEFT JOIN user_friends x_friends ON x_friends.user_id = uf.friend_id
LEFT JOIN user1_friends u1 ON x_friends.friend_id = u1.friend_id
GROUP BY uf.friend_id
ORDER BY uf.friend_id;

执行后会得到你预期的输出：

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

方案二：兼容低版本MySQL（不使用CTE） #

如果你的MySQL版本低于8.0，不支持CTE，可以用子查询嵌套的方式，核心是先把user2的好友列表抽成独立子查询，避免跨层级字段引用：

SELECT 
    f2.friend_id AS user_id,
    (
        SELECT COUNT(*)
        FROM (
            SELECT user_two_id AS friend FROM friends WHERE user_one_id = f2.friend_id
            UNION
            SELECT user_one_id AS friend FROM friends WHERE user_two_id = f2.friend_id
        ) AS x_friends
        JOIN (
            SELECT user_two_id AS friend FROM friends WHERE user_one_id = 1
            UNION
            SELECT user_one_id AS friend FROM friends WHERE user_two_id = 1
        ) AS user1_friends ON x_friends.friend = user1_friends.friend
    ) AS mutual_count
FROM (
    -- 先获取user2的所有好友并去重
    SELECT user_one_id AS friend_id FROM friends WHERE user_two_id = 2
    UNION
    SELECT user_two_id AS friend_id FROM friends WHERE user_one_id = 2
) AS f2
ORDER BY f2.friend_id;

原SQL报错的深层原因 #

你原来的SQL中，最内层的SELECT user_two_id friendID FROM friends WHERE user_one_id = users.user_id属于第三层级子查询，而users.user_id在最外层的SELECT语句中。MySQL的作用域规则不允许跨这么多层级引用字段，我们的解决方案通过先提取user2的好友列表作为独立子查询，让内层子查询可以直接引用该子查询的字段（f2.friend_id），从而避免了作用域冲突。

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