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

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

我有3个表表示“用户”、“角色”和多对多的“UsersInRoles”——键是:UserId、RoleId;pertinant列:用户名、RoleName

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).

在管理html应用程序中，我想显示所有用户及其所在角色的列表。在SQL中，我尝试构造一个查询来返回这个信息。应该对角色列表进行分隔，以便我可以进行适当的表示操作(取决于表示平台，比如用BR标记替换分隔符)。

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.

为用户列表使用一个select，然后为每个用户使用单独的select来获取角色是很直接的，但是尝试构造一个select来输出下面的内容让我很为难。

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

Thanks in advance,
--Ed

提前谢谢,

--EDIT--
now working with the following query (thanks to all, esp. Raymund & his blog):

——编辑——现在处理以下查询(感谢所有人，尤其是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

3 个解决方案

#1

Heres your solution:

这是您的解决方案:

Converting / Parsing Rows to Delimited string column in SQL

将行转换成SQL中分隔的字符串列。

EDIT

编辑

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

#2

SQL Server 2005+:

SELECT u.user_id,
       u.username,
       STUFF(ISNULL(SELECT ', ' + r.role_name
                      FROM USERSINROLES uir
                  JOIN ROLES r ON r.role_id = uir.role_id
                 WHERE uir.user_id = u.user_id
              GROUP BY r.role_name
                   FOR XML PATH ('')), ''), 1, 2, '')
  FROM USERS u

#3

Since @OMG Ponies pointed out that my original "pivoting" response was off the mark for this question, let me defer to him for the solution. However, I'd still like to mention an alternative:

自从@OMG Ponies指出，我最初的“转向”反应已经偏离了这个问题的标记，让我尊重他的解决方案。然而，我仍然想提及另一种选择:

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).

应该对角色列表进行分隔，以便我可以进行适当的表示操作(取决于表示平台，比如用BR标记替换分隔符)。

If you're providing delimiters just so your presentation layer can (potentially) break the combined values back apart, why not just return them in a separate set, or just return the result of joining to your many-to-many table (leaving you with a bunch of duplicates), and just process the results in your presentation layer?

如果你提供分隔符这样表示层可以(可能)打破组合值,为什么不直接返回他们在一个单独的组,或者只是返回结果加入到你的多对多表(留给你一堆重复),就表示层处理结果吗?

Two arguments for this:

这两个参数:

• String manipulation in something like C# is typically faster / less-painful than the same in SQL
• 类似c#的字符串操作通常比SQL中的字符串操作要快/不那么麻烦
• Better separation of concerns - your data layer can simply return a set of roles-per-user, without concerning itself with how that data will be presented
• 更好的关注点分离——您的数据层可以简单地返回一组每个用户的滚动，而不必考虑数据将如何显示

Neither of those might be compelling, in your case, but I hope I've provided food for thought.

在你的例子中，这两种方法都不是很有说服力，但我希望我已经提供了思考的食粮。