mysql - 如何编写 SQL 查询从多个表中获取信息？

Question

我需要编写一个满足以下条件的查询:

给定一个 uid 列表 (6,8,10)，获取具有“管理用户”权限的用户的 name_first 和 name_last。

CREATE TABLE users (
uid INT(11) AUTO_INCREMENT,
status TINYINT(3) NOT NULL DEFAULT 1,
name_first VARCHAR(256) DEFAULT '',
name_last VARCHAR(256) DEFAULT '',
email VARCHAR(256) DEFAULT '',
PRIMARY KEY (`uid`)
);

CREATE TABLE role(
rid INT(11) AUTO_INCREMENT,
name VARCHAR(256),
PRIMARY KEY `rid` (`rid`)
);

CREATE TABLE users_roles (
uid INT(11),
rid INT(11),
PRIMARY KEY (`uid`),
KEY `rid` (`rid`)
);

CREATE TABLE permissions (
rid INT(11),
permission VARCHAR(64),
UNIQUE KEY (`rid`, `permission`)
);

INSERT INTO users (uid, status, name_first, name_last, email) 
VALUES 
(1, 1, 'Joe', 'Montana', ''),
(2, 1, 'Eli', 'Manning', ''), 
(3, 1, 'Peyton', 'Manning', ''), 
(4, 1, 'Steve', 'Young', ''), 
(5, 1, 'John', 'Elway', ''), 
(6, 1, 'Robert', 'Griffin', ''), 
(7, 1, 'Joe', 'Montana', ''),
(8, 1, 'Tom', 'Brady', ''),
(9, 1, 'Drew', 'Brees', ''),
(10, 1, 'Andrew', 'Luck', '');

INSERT INTO role (rid, name) 
VALUES
(1, 'Teacher'),
(2, 'Athletic Directory'),
(3, 'Administrator');

INSERT INTO permissions (rid, permission) VALUES
(1, 'Administer users'),
(1, 'Administer courses'),
(1, 'Build Unicorns'),
(2, 'Administer groups'),
(3, 'Administer users'),
(3, 'Administer school');

INSERT INTO users_roles (uid, rid) VALUES
(1, 3),
(2, 1),
(3, 3),
(4, 1),
(5, 1),
(6, 2),
(7, 2),
(8, 3),
(10, 1);

这是我到目前为止所拥有的:

SELECT name_first, name_last FROM users
JOIN users_roles
ON users.uid = users_roles.uid
JOIN role
ON role.rid = users_roles.rid
JOIN permissions
ON users.uid = permissions.rid
WHERE users.uid = 8 AND permissions.permission = 'Administer users';

这是我第一次尝试编写 SQL，我在尝试让它工作时感到困惑。如有任何帮助，我们将不胜感激。

Answer 1

我添加了一个额外的列，以防万一您需要它。我创建了临时表进行测试，因此只需从查询中删除哈希标签即可:

SELECT
     U.name_first
    ,U.name_last
    ,P.permission PermissionName
    ,R.name RoleName
FROM
    #users U
    JOIN #users_roles UR
        ON  U.uid= UR.uid
    LEFT JOIN #role R
        ON  UR.rid = R.rid
    LEFT JOIN #permissions P
        ON  UR.rid = P.rid
WHERE
    U.uid IN (6,8,10)
    AND P.permission = 'Administer users'
;