mysql 无法将列从查询传递到嵌套子查询

Question

我想要的是找到与 user2 (id = 2) 关联的每个 friend ，然后找到每个 user2 friend 和 user1 之间的共同 friend 数。

mysql 查找 user2 好友并获取与 user1 的好友的 mutual_count(与每个 user2 好友)

表用户

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

同 table friend

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

预期输出: friends of user2//( friend name , friend id, current friend id and user1 (id = 1))

username | user_id  | mutual_count
------------------------
user3   |  3        | 3     // user3 and user1 mutual ->  (user4,user5,user6)
user4   |  4        | 2     // user4 and user1 mutual ->  (user3 ,user5)
user5   |  5        | 2     // user5 and user1 mutual ->  (user3 ,user4)
user6   |  6        | 1     // user6 and user1 mutual ->  ( user3)
user7   |  7        | 0    // user7 and user1 mutual ->   (0)

mysql语句

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

我得到的错误“where 子句”中的未知列“users.user_id”获取 mutual_count 的问题子查询无法知道 users.user_id

感谢任何帮助

Answer 1

我修改了你的尝试以获得所需的输出(但可能有更好的方法来获得你想要的)。

主要问题似乎是顶部外部查询中的users 表不是所有子查询都可以访问的。所以我修改了需要这张表的子查询，让它可以通过直接外层查询的WHERE子句访问users表。

SELECT  users.username,users.user_id,

    (SELECT count(a.friendID) FROM
        (   SELECT user_two_id friendID, user_one_id where_id FROM friends
            UNION 
            SELECT user_one_id friendID, user_two_id where_id FROM friends
        ) 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
        WHERE a.where_id = users.user_id
    ) 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

输出:

+----------+---------+--------------+
| username | user_id | mutual_count |
+----------+---------+--------------+
|   user3  |    3    |       3      |
+----------+---------+--------------+
|   user4  |    4    |       2      |
+----------+---------+--------------+
|   user5  |    5    |       2      |
+----------+---------+--------------+
|   user6  |    6    |       1      |
+----------+---------+--------------+
|   user7  |    7    |       0      |
+----------+---------+--------------+

修改后的行:

第 4 行: SELECT user_two_id friendID, user_one_id where_id FROM friends

第 6 行: SELECT user_one_id friendID, user_two_id where_id FROM friends

第 14 行: WHERE a.where_id = users.user_id