将管理员角色包含在角色表的用户表中

发布于 2024-08-18 05:07:48 字数 657 浏览 4 评论 0原文

有没有办法像这样查询用户表：

| id | username |
-----------------
| 1  | user1    |
| 2  | user2    |
| 3  | user3    |

和 user_roles 表：

| id_user | id_role |
---------------------
|    1    |    1    |
|    1    |    2    |
|    1    |    3    |
|    2    |    2    |
|    3    |    1    |

假设 id = 1 的角色是管理员角色，产生如下所示的结果：

| id | username | admin |
-------------------------
| 1  | user1    |   Y   |
| 2  | user2    |   N   |
| 3  | user3    |   Y   |

我认为可以使用嵌套 SELECT 语句来完成，但我想知道如果可以使用 JOIN 的话。

编辑：管理列值不必是 Y 或 N，它可以是管理员角色 ID (1) 或 NULL 或任何能让我知道用户是否是管理员的值

原文

Is there a way to query users table like this:

| id | username |
-----------------
| 1  | user1    |
| 2  | user2    |
| 3  | user3    |

and user_roles table:

| id_user | id_role |
---------------------
|    1    |    1    |
|    1    |    2    |
|    1    |    3    |
|    2    |    2    |
|    3    |    1    |

assuming that role with id = 1 is an admin role, to produce result that looks like this:

| id | username | admin |
-------------------------
| 1  | user1    |   Y   |
| 2  | user2    |   N   |
| 3  | user3    |   Y   |

I think it can be done using nested SELECT statements, but I was wondering if it's doable using JOIN.

Edit:
The admin column value doesn't have to be Y or N, it can be admin role id (1) or NULL or whatever that will let me know if user is an admin

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

π浅易 2024-08-25 05:07:48

我会尝试这个：

select u.id, u.username, if (id_role is null,  'N', 'Y') as is_admin
  from users u
  left outer join user_roles r
    on u.id = r.id_user and r.id_role = 1

但我不是100%确定。

I would try this:

select u.id, u.username, if (id_role is null,  'N', 'Y') as is_admin
  from users u
  left outer join user_roles r
    on u.id = r.id_user and r.id_role = 1

But I'm not 100% sure.

回复收藏 0 原文

三生一梦 2024-08-25 05:07:48

好吧，您可以像这样加入，这将在结果中为您提供 id_role。

SELECT u.*, r.id_role
FROM users u
LEFT JOIN user_roles r
ON u.id=r.id_user

您可以添加 WHERE r.id_role=1 来仅获取管理员等。

但是要根据需要将管理员设置为“Y”或“N”，您可以使用 IF< /code> id_role 是否为 1。

SELECT u.*, IF(r.id_role = 1, "Y", "N") as admin
FROM users u
LEFT JOIN user_roles r
ON u.id=r.id_user

Well, you can join like this, which will give you the id_role in the result.

SELECT u.*, r.id_role
FROM users u
LEFT JOIN user_roles r
ON u.id=r.id_user

You can add WHERE r.id_role=1 to get just the admins, etc.

But to get the admin as "Y" or "N" as you wanted, you can use an IF on whether the id_role is 1 or not.

SELECT u.*, IF(r.id_role = 1, "Y", "N") as admin
FROM users u
LEFT JOIN user_roles r
ON u.id=r.id_user

回复收藏 0 原文

土豪 2024-08-25 05:07:48

select u.id_user, if(count(1) > 0, 'Y', 'N')
from user u left outer join user_roles ur on u.user_id = ur.user_roles
where ur.id_role=1
group by u.id_user

我没有使用 mysql，所以只是用 google 搜索它有“if”函数，如果这是错误的，请替换为 DECODE、COALESCE 或类似函数。

select u.id_user, if(count(1) > 0, 'Y', 'N')
from user u left outer join user_roles ur on u.user_id = ur.user_roles
where ur.id_role=1
group by u.id_user

I'm not using mysql, so just googled it has the 'if' function, if that's wrong, replace with DECODE, COALESCE or alike.

回复收藏 0 原文

~没有更多了~