mysql - MySQL查询错误1054-未知列

我在mysql中运行查询失败。我故意不想做。。。
询问

select rtu.role_id
from roles r
where id =(select role_id
           from roles_to_user rtu
           where user_id=1)

错误

ERROR 1054 (42S22): Unknown column 'rtu.role_id' in 'field list'

向用户描述角色：

+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| user_id | int(11) | NO   | PRI | NULL    |       |
| role_id | int(11) | NO   | PRI | NULL    |       |
+---------+---------+------+-----+---------+-------+

展开卷轴：

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(80) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

最佳答案

不能像这样使用内部select中的列。改为选择r.id

select r.id
from roles r
where id = (select role_id
            from roles_to_user rtu
            where user_id = 1)

顺便说一句，如果内部select返回的记录超过1条，则失败。使用join代替

select rtu.role_id
from roles r
inner join roles_to_user rtu on rtu.role_id = r.id
where rtu.user_id = 1