使用 GROUP_CONCAT 和 LEFT JOIN 约束 SQL 结果

Question

我需要限制有关用户角色(用户或管理员)的用户查询。

在数据库中，有一个表user_scope包含所有用户 ID 和分配的角色(每个用户都有一个条目，其中 1 表示用户，有些用户有第二个条目，其中 2 表示管理员)。我现在无法更改该数据库体系结构。

这是我到目前为止users表上的选择 连接来自其他表的数据

SELECT
u.id,
GROUP_CONCAT(DISTINCT scopes.scope ORDER BY scopes.id ASC SEPARATOR ' ') as scope
FROM users as u
LEFT JOIN user_scope on user_scope.user_id = u.id
LEFT JOIN scopes on scopes.id = user_scope.scope_id

该表users

+----+--------+
| id | parent |
+----+--------+
|  1 | Alex   |
|  2 | Marc   |
|  3 | Cath   |
+----+--------+

该表user_scope

+---------+----------+
| user_id | scope_id |
+---------+----------+
|  1      | 1        |
|  1      | 2        |
|  2      | 1        |
|  3      | 1        |
+---------+----------+

该表scopes

+----+--------+
| id | scope  |
+----+--------+
|  1 | user   |
|  2 | admin  |
+----+--------+

这将产生这样的东西

+----+------------+
| id | scope      |
+----+------------+
|  1 | user admin |
|  2 | user       |
|  3 | user       |
+----+------------+

当我想根据一个特定角色过滤结果时，就会出现问题。我试过这个

SELECT
u.id,
GROUP_CONCAT(DISTINCT scopes.scope ORDER BY scopes.id ASC SEPARATOR ' ') as scope
FROM users as u
LEFT JOIN user_scope on user_scope.user_id = u.id
LEFT JOIN scopes on scopes.id = user_scope.scope_id and scopes.id = 2

或分别为 1。但是，这不会减少返回的行数，而只会对没有范围admin的用户的行进行空运算。我也尝试使用CASE但我不能在 WHERE 语句中使用它。

如何减少在此上下文中返回的行？真的很感激帮助。

Answer 1

使用having子句：

SELECT u.id,
GROUP_CONCAT(DISTINCT s.scope ORDER BY s.id ASC SEPARATOR ' ') as scopes
FROM users u LEFT JOIN
user_scope us
ON us.user_id = u.id LEFT JOIN
scopes s
ON s.id = us.scope_id
GROUP BY u.id
HAVING MAX(s.id = 2) > 0;