☰

建议查询

tag-icon • tag-icon mysql sql mysql5
建议查询

我有两张正在交互的表。

第一个表：acl_permissions（只是一个普通表）。

+-----------------------------------+
|           acl_permissions         |
+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  1 | Add User    | addUser        |
|  2 | Edit User   | editUser       |
|  3 | Delete User | deleteUser     |
|  4 | View User   | viewUser       |
|  5 | Test Name   | testPermission |
+----+-------------+----------------+

第二张表：acl_group_permissions 是一个连接表。

+--------------------------------+
|      acl_group_permissions     |
+----+----------+----------------+
| id | group_id | permissions_id |
+----+----------+----------------+
|  1 |     1    |        1       |
|  2 |     1    |        2       |
|  3 |     1    |        3       |
|  4 |     1    |        4       |
|  5 |     2    |        4       |
|  6 |     2    |        5       |
+----+----------+----------------+

我有一个查询，显示 group_id‘1’当前允许的所有权限。

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id = 1

结果是：

+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  1 | Add User    | addUser        |
|  2 | Edit User   | editUser       |
|  3 | Delete User | deleteUser     |
|  4 | View User   | viewUser       |
+----+-------------+----------------+

我的问题

我想要一个查询来显示 group_id‘1’不允许的所有权限。

期望的结果是：

+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  5 | Test Name   | testPermission |
+----+-------------+----------------+

我最接近的是：

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
LEFT OUTER JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id IS NULL

结果是：

+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  4 | View User   | viewUser       |
|  5 | Test Name   | testPermission |
+----+-------------+----------------+

我如何将 group_id '1' 输入到查询中以消除 id 4。

我已经在 Google 上搜索了很多，但我找到的例子和解决方案与我的情况很接近但又很准确。

IE：使用连接表。我的 MySQL 连接类型信息图表也没什么帮助。

救命..！我的大脑快融化了..！

答案1

不要使用连接逻辑，而是使用更简单的逻辑：

选择 group_1 拥有的所有 permissions_id（来自 acl_group_permissions），然后选择所有不在所选中的 acl_permissions id。翻译成 SQL 后，它变成：

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
WHERE acl_permissions.id NOT 
IN (
    SELECT permissions_id
    FROM acl_group_permissions
    WHERE group_id = 1
)

答案2

不是最好的，但可以

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id != 1 and acl_permissions.id 
NOT IN (SELECT DISTINCT acl_permissions.id
FROM acl_permissions
JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id = 1)

答案3

建议查询

SELECT A.*
FROM acl_permissions A LEFT JOIN
(SELECT * FROM acl_group_permissions WHERE group_id=1) B
ON A.id = B.permissions_id WHERE B.id IS NULL;

您的样本数据

DROP DATABASE IF EXISTS matt;
CREATE DATABASE matt;
USE matt
CREATE TABLE acl_permissions
(
    id int not null auto_increment,
    name varchar(32),
    permission varchar(32),
    primary key (id)
);
CREATE TABLE acl_group_permissions
(
    id int not null auto_increment,
    group_id int not null,
    permissions_id int not null,
    primary key (id)
);
INSERT INTO acl_permissions
(name,permission) VALUES
('Add User'   ,'addUser'),
('Edit User'  ,'editUser'),
('Delete User','deleteUser'),
('View User'  ,'viewUser'),
('Test Name'  ,'testPermission');
INSERT INTO acl_group_permissions
(group_id,permissions_id) VALUES
(1,1),(1,2),(1,3),(1,4),(2,4),(2,5);
SELECT * FROM acl_permissions;
SELECT * FROM acl_group_permissions;

您的样本数据已加载

mysql> DROP DATABASE IF EXISTS matt;
Query OK, 2 rows affected (0.39 sec)

mysql> CREATE DATABASE matt;
Query OK, 1 row affected (0.00 sec)

mysql> USE matt
Database changed
mysql> CREATE TABLE acl_permissions
    -> (
    ->     id int not null auto_increment,
    ->     name varchar(32),
    ->     permission varchar(32),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> CREATE TABLE acl_group_permissions
    -> (
    ->     id int not null auto_increment,
    ->     group_id int not null,
    ->     permissions_id int not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO acl_permissions
    -> (name,permission) VALUES
    -> ('Add User'   ,'addUser'),
    -> ('Edit User'  ,'editUser'),
    -> ('Delete User','deleteUser'),
    -> ('View User'  ,'viewUser'),
    -> ('Test Name'  ,'testPermission');
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO acl_group_permissions
    -> (group_id,permissions_id) VALUES
    -> (1,1),(1,2),(1,3),(1,4),(2,4),(2,5);
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM acl_permissions;
+----+-------------+----------------+
| id | name        | permission     |
+----+-------------+----------------+
|  1 | Add User    | addUser        |
|  2 | Edit User   | editUser       |
|  3 | Delete User | deleteUser     |
|  4 | View User   | viewUser       |
|  5 | Test Name   | testPermission |
+----+-------------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM acl_group_permissions;
+----+----------+----------------+
| id | group_id | permissions_id |
+----+----------+----------------+
|  1 |        1 |              1 |
|  2 |        1 |              2 |
|  3 |        1 |              3 |
|  4 |        1 |              4 |
|  5 |        2 |              4 |
|  6 |        2 |              5 |
+----+----------+----------------+
6 rows in set (0.00 sec)

mysql>

建议的查询已执行

mysql> SELECT A.*
    -> FROM acl_permissions A LEFT JOIN
    -> (SELECT * FROM acl_group_permissions WHERE group_id=1) B
    -> ON A.id = B.permissions_id WHERE B.id IS NULL;
+----+-----------+----------------+
| id | name      | permission     |
+----+-----------+----------------+
|  5 | Test Name | testPermission |
+----+-----------+----------------+
1 row in set (0.00 sec)

mysql>

试一试 ！！！

相关内容