包含用户、角色和权限的 SQL SELECT 语句

Question

假设我有表格:

1. 用户
2. Users_in_Roles
3. 角色
4. Rights_in_Roles
5. 权利

键是标准的(UserFk、RoleFk、RightFk)

问题是:如何获取角色中所有具有正确 X (id = 100) 的用户

我不知道如何解决这个问题。请帮忙，对不起我的英语。

SELECT [dbo].[System_Users].[Id]
  ,[UserName]
  ,[FirstName]
  ,[LastName]
  ,[Email]
  ,RoleFk
  ,[dbo].[System_Roles].Name
  FROM [dbo].[System_Users]
INNER JOIN [dbo].[System_Roles_System_Users]
ON [dbo].[System_Roles_System_Users].UserFk = [dbo].[System_Users].Id
INNER JOIN [dbo].[System_Roles]
ON [dbo].[System_Roles].Id = [dbo].[System_Roles_System_Users].RoleFk

WHERE ?

---

我试过那样的东西，你能告诉我哪里错了吗？

SELECT 
DISTINCT System_Users.Id,
System_Users.FullName
FROM System_Users
INNER JOIN Dict_Rights_System_Users
ON System_Users.Id = Dict_Rights_System_Users.UserFk
INNER JOIN System_Roles_System_Users
ON System_Roles_System_Users.UserFk = System_Users.Id
WHERE
RightFk = 136
OR
136 IN (SELECT Dict_Rights_System_Roles.RightFk FROM Dict_Rights_System_Roles WHERE             
Dict_Rights_System_Roles.RoleFk = System_Roles_System_Users.RoleFk)
ORDER BY System_Users.FullName ASC

Answer 1

您将需要JOIN 键关系表。基本结构将是:

select u.Id, 
  u.UserName,
  u.FirstName,
  u.LastName,
  u.Email,
  r.RoleFk,
  r.Name RoleName,
  rt.Name RightName
from users u
inner join users_in_roles ur
  on u.id = ur.userfk
inner join roles r
  on ur.rolefk = r.id
inner join rights_in_roles rr
  on r.rolefk = rr.rolefk
inner join rights rt
  on rr.rightfk = rt.id
where rt.id = 100

如果您在学习 JOIN 语法方面需要帮助，这里是一个很好的引用:

• A Visual Explanation of SQL Joins