sql - 帮我解决这个 SQL 查询

Question

3个表定义如下:

用户

User_ID      INT
First_Name   VARCHAR
Last_Name    VARCHAR
Email        VARCHAR

角色

Role_ID      INT
Role_Name    VARCHAR
Access_Level INT

角色_用户

User_ID      INT
Role_ID      INT

Roles_Users 是Users 和Roles 之间的多对多链接表。我想撤回以下信息:

First_Name, Last_Name, Email, Role_Name

我目前拥有的是:

SELECT 
    U.First_Name,
    U.Last_Name,
    U.Email,
    R.Name AS Role_Name
FROM Users U
INNER JOIN Roles_Users RU ON U.User_ID = RU.User_ID
INNER JOIN Roles R ON RU.Role_ID = R.Role_ID

棘手的部分(至少对我而言)是我只想为该特定用户拉回 Role_Name 和 MIN(Access_Level)。所以基本上我想要提取的记录集将只列出一次每个用户的最低访问级别角色名称。

我敢肯定这很简单，但现在让我很困惑。

谢谢

Answer 1

您可以将 CTE(公用表表达式)与 ROW_NUMBER 结合使用像这样的窗口函数:

;WITH MinAccessData AS
(
SELECT 
    U.First_Name,
    U.Last_Name,
    U.Email,
    R.Name AS Role_Name,
    ROW_NUMBER() OVER(PARTITION BY U.User_ID ORDER BY R.Access_Level) AS RowNum
FROM Users U
INNER JOIN Roles_Users RU ON U.User_ID = RU.User_ID
INNER JOIN Roles R ON RU.Role_ID = R.Role_ID
)
SELECT *
FROM MinAccessData
WHERE RowNum = 1

CTE 按 User_ID 对您的数据进行“分区” ，例如每个用户都有一个“分区”。在该分区内，角色按 Access_level 排序最小的是第一个 - 所以它得到 RowNum = 1 - 对于每个用户。

然后您从该 CTE 中选择所有那些条目，其中 RowNum = 1 - 这会为每个用户提供最小 Access_Level 的所有条目值(value)。