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

问题描述：

考虑（6,8,10）的UID的列表，获取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第一次尝试，我已经糊涂自己努力使其发挥作用。任何帮助表示赞赏。

检查你加入条件'users.uid = permissions.rid'，你期望这样做会怎样？ –

不，我进一步查看后，权限表没有使用uid作为外键。 –

哪种RDBMS适用于？ * SQL *是一个标准 - 但供应商经常以不同的方式实现它，所以请添加一个标签来指定您是使用'mysql'，'postgresql'，'sql-server'，'oracle'还是'db2' - 其他完全。 –

答

ON users.uid = permissions.rid

应该

ON role.rid = permissions.rid

因为如果你想看到你需要使用LEFT JOIN（外连接），而不是INNER JOIN NULL值不是所有的记录，涉及到其他表。

如果您想查看所有记录，或者它与INNER JOIN相同，您应该将permission ='admin ...'移至开启状态。

SELECT 
    * 
FROM 
    users u 
    LEFT JOIN users_roles ur 
    ON u.uid = ur.uid 
    LEFT JOIN role r 
    ON ur.rid = r.rid 
    LEFT JOIN permissions p 
    ON r.rid = p.rid 
    AND p.permission = 'Administer users' 
WHERE 
    u.uid IN (6,8,10);

当我拥有users.uid = 8时适用，但我怎样才能检查uid 6和10？我写出了单独的WHERE语句并出现错误。 –

使用左连接而不是内连接，因为不是所有的用户都被分配到一个角色和权限 – Matt

我试过你在上面发布的东西，它没有工作，它给了我所有的名字和姓氏，而不仅仅是用于uid 6的，8和10. http://sqlfiddle.com/#!9/f6955/314 –

答

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

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' 
;

，因为你的where子句中有一个限制结果的条件，你有一个LEFT JOIN对你基本上是做一个内部连接。您需要将p.permission ='admin ...'移动到加入的ON条件部分 – Matt

@Matt引用左连接的WHERE子句的使用是内连接是正确的。然而，我这样做是基于我的假设，我希望看到当我不使用WHERE子句时这些表如何交互。角色和权限表是相互独立的，所以我使用了两个左连接。如果您没有这种需求，或者您的案例没有任何好处，则可以使用连接。但是，在任何情况下，您都不必将条件从WHERE子句移动到ON条件。它的基础是第三方阅读的可能性更大，而且更具动态性。祝你好运！ –

@Matt我不反对你。我只能说出过去对我有用的东西。通常最终用户的需求会发生变化，所以我会尽量平衡代码的效率和代码，这些代码可以根据最终用户的变化需求快速轻松地进行更改。但是，如果我希望在代码中更简洁，我可能仍然不会将权限从WHERE子句移动到ON子句，但它可能只是样式的差异。 –

答

试试这个但你不会得到UID（6），因为它不具有“管理用户”权限。

选择name_first，从用户name_last其中uid在（选择从其中除掉 users_roles UID（选择权限，其中权限= '管理用户' RID））和（6,8,10）的UID;

if you need 6 too then UPDATE'users_roles' SET'rid' ='1'WHERE' users_roles'.'uid' = 6; – Rakesh

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

相关推荐