SQL:从单个查询中列出多对多

2021-09-10 00:00:00 tsql sql-server

我有 3 个表，分别代表Users"、Roles"和多对多的UsersInRoles"——键为:UserId、RoleId；相关列:UserName、RoleName

I've got 3 tables representing "Users", "Roles", and the many-to-many "UsersInRoles" - with keys: UserId, RoleId; pertinant columns: UserName, RoleName

在 admin html 应用程序中，我想显示所有用户及其所在角色的列表.我试图从 SQL 构建一个将返回此信息的查询.应该对角色列表进行分隔，以便我可以进行适当的演示操作(取决于演示平台，例如用 BR 标记替换分隔符).

In the admin html app, I want to show a list of all users and the roles they are in. From SQL, I'm trying to construct a single query that will return this information. The list of roles should be delimited so I can do the appropriate presentation manipulation (depending on presentation platform, like replace delimiter with BR tag).

对用户列表使用一个选择，然后为每个用户单独选择来获取角色是很简单的，但是尝试构建一个输出以下内容的选择让我感到困惑.

Using one select for the list of users and then individual selects for each user to get the roles is straight-forward, but trying to construct a single select that outputs the below has got me stumped.

UserId  UserName  Roles
------  --------  -----
1       user1     Admin,Guest,PowerUser
2       user2     Guest
3       user3
4       user4     PowerUser,Guest

提前致谢，
--埃德

Thanks in advance,
--Ed

--编辑--
现在使用以下查询(感谢所有人，特别是 Raymund & 他的博客):

WITH RolesList AS
(
  SELECT u.UserName,
  (
    SELECT r.RoleName + ',' AS 'data()'
    FROM Roles r
    JOIN UsersInRoles ur ON ur.RoleId = r.RoleId
    WHERE ur.UserId = u.UserId FOR XML PATH('')
  ) AS RolesCSV
  FROM Users u
) SELECT UserName, LEFT(RolesCSV, LEN(RolesCSV)-1) AS RolesCSV FROM RolesList

推荐答案

这是您的解决方案:

转换/将行解析为 SQL 中的分隔字符串列

编辑

如果您需要进一步澄清，这里是答案

If you need further clarity here is the answer

WITH UserList as
(
SELECT UserID, UserName,
(SELECT 
RoleName + ',' AS 'data()'
FROM Roles
INNER JOIN 
UsersInRoles 
ON 
Roles.RoleID = UsersInRoles.RoleID
WHERE
UsersInRoles.UserID = Users.UserID FOR XML PATH('')) AS RoleCSV
FROM Users
)
SELECT UserID, UserName, LEFT(RoleCSV, LEN(RoleCSV)-1) as RoleCSV from UserList

相关文章