mysql - 为什么我的子查询无法访问外部查询中的列

Question

我正在尝试让所有不具有至少一个值为 secure=1 的角色的用户

示例:假设我们有这些数据:

用户:

id | name | email |password

500 | admin | admin@gmail.com | something

600 | someuser | test@gmail.com | something

角色:

id | name | secured

1 | Administrator | 1

2 | Guest | 0

3 | Normal | 0

角色用户:

user_id | role_id

500 | 1

500 | 3

600 | 2

600 | 3

我希望查询返回:唯一 ID 为 600 的用户

查询:

    SELECT * 
    FROM `users` 
    WHERE (SELECT MAX(roles.secured) FROM roles 
           JOIN role_user 
           ON role_user.user_id = users.id 
           AND role_user.role_id = roles.id) != 1

返回mysql错误:

1054 - Unknown column 'users.id' in 'on clause'

你能帮忙吗？

架构:

用户:

Column  Type    Null    Default Comments
id (Primary)    int(10) No      
name    varchar(255)    No      
email   varchar(255)    No      
password    varchar(60) No      
blocked tinyint(1)  No  0   
remember_token  varchar(100)    Yes NULL    


roles:

Column  Type    Null    Default Comments
id (Primary)    int(10) No      
name    varchar(255)    No      
secured tinyint(1)  No  1   
display_name    varchar(255)    Yes NULL    
description varchar(255)    Yes NULL    


role_user:

Column  Type    Null    Default Comments
user_id (Primary)   int(10) No      
role_id (Primary)   int(10) No      

Answer 1

根据您给定的数据，假设您所需的输出是返回“安全”程度最高的用户的详细信息。您需要执行两次连接，一次连接到 role_user 表中的每个表。然后将 where 放在最后以仅返回最大值条目。

      SELECT * 
      FROM `users` 
       JOIN role_user 
      ON users.id = role_user.user_id 
       JOIN roles
      ON roles.id = role_user.role_id
      WHERE roles.secured = (SELECT MAX(roles.secured) FROM roles

要获取所有安全值不为 1 的用户，您应该获取安全值为 1 的用户，然后获取所有 id NOT IN 的用户(您在此处获取所有用户查询)。

例如:

      SELECT * 
      FROM `users` 

      WHERE users.id NOT IN (SELECT * FROM users
                              JOIN role_user 
                             ON users.id = role_user.user_id 
                              JOIN roles
                             ON roles.id = role_user.role_id
                             WHERE roles.secured = 1)