MySQL子查询无法识别外层users.user_id字段问题求助

我来帮你搞定这个子查询作用域的问题！先理清楚报错的原因，再一步步给出修复方案。

问题场景回顾 #

你需要获取user2（id=2）的所有好友，并统计每个好友与user1（id=1）的共同好友数量。现有两张表：

users表 #

user_id | username
------------------
1       | user1
2       | user2
3       | user3
4       | user4
5       | user5
6       | user6
7       | user7

friends表 #

user_one_id | user_two_id
------------------------
1           | 4
1           | 5
1           | 6
2           | 3
2           | 4
3           | 1
3           | 4
5           | 2
5           | 3
5           | 4
6           | 2
6           | 3
7           | 2

预期输出：

username | user_id | mutual_count
------------------------
user3    | 3       | 3 // user3和user1的共同好友为(user4,user5,user6)
user4    | 4       | 2 // user4和user1的共同好友为(user3 ,user5)
user5    | 5       | 2 // user5和user1的共同好友为(user3 ,user4)
user6    | 6       | 1 // user6和user1的共同好友为( user3)
user7    | 7       | 0 // user7和user1无共同好友

你最初写的SQL触发了报错：Unknown column 'users.user_id' in 'where clause'，因为内层子查询没法识别最外层的users.user_id字段：

SELECT users.username,users.user_id,
(SELECT count(a.friendID)
 FROM (
     SELECT user_two_id friendID FROM friends WHERE user_one_id =users.user_id
     UNION
     SELECT user_one_id friendID FROM friends WHERE user_two_id = users.user_id
 ) AS a
 JOIN (
     SELECT user_two_id friendID FROM friends WHERE user_one_id = 1
     UNION
     SELECT user_one_id friendID FROM friends WHERE user_two_id = 1
 ) AS b ON a.friendID = b.friendID
) as mutual_count
FROM friends
LEFT JOIN users ON friends.user_one_id = users.user_id or friends.user_two_id = users.user_id
WHERE (friends.user_one_id = 2 OR friends.user_two_id = 2) AND users.user_id !=2
order by user_id

问题根源 #

MySQL的子查询作用域规则是：多层嵌套的子查询只能直接引用上一层的字段，无法跨层引用最外层的表字段。你这里嵌套了两层子查询（a是内层，外面还有count的子查询），所以内层的a子查询找不到最外层的users.user_id。

修复方案 #

我们换一种思路，先把好友关系整理成统一的双向格式，再基于这个来统计，避开多层嵌套的作用域问题。

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

CTE（公共表表达式）让逻辑更清晰，而且不存在跨层字段引用的问题：

-- 先筛选出user2的所有好友
WITH user2_friends AS (
    SELECT 
        CASE WHEN user_one_id = 2 THEN user_two_id ELSE user_one_id END AS friend_id
    FROM friends
    WHERE user_one_id = 2 OR user_two_id = 2
),
-- 筛选出user1的所有好友
user1_friends AS (
    SELECT 
        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
),
-- 生成所有用户的双向好友关系（避免漏查单向好友）
all_friendships 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
)
SELECT 
    u.username,
    u.user_id,
    -- 统计当前好友与user1的共同好友数，没有则返回0
    COALESCE(
        (SELECT COUNT(DISTINCT af.friend_id)
         FROM all_friendships af
         JOIN user1_friends uf1 ON af.friend_id = uf1.friend_id
         WHERE af.user_id = u.user_id),
        0
    ) AS mutual_count
FROM user2_friends uf2
JOIN users u ON uf2.friend_id = u.user_id
ORDER BY u.user_id;

方案2：兼容MySQL 5.x版本（不用CTE） #

如果你的MySQL版本不支持CTE，可以用子查询代替，逻辑和上面一致：

SELECT 
    u.username,
    u.user_id,
    COALESCE(
        (SELECT COUNT(DISTINCT af.friend_id)
         FROM (
             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
         ) af
         JOIN (
             SELECT 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 af.friend_id = uf1.friend_id
         WHERE af.user_id = u.user_id),
        0
    ) AS mutual_count
FROM (
    SELECT CASE 
               WHEN user_one_id = 2 THEN user_two_id 
               ELSE user_one_id 
           END AS friend_id
    FROM friends
    WHERE user_one_id = 2 OR user_two_id = 2
) uf2
JOIN users u ON uf2.friend_id = u.user_id
ORDER BY u.user_id;

这两个方案都能正确返回你想要的结果，而且避免了原SQL中的作用域问题。

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