代码之家 › 专栏 › 技术社区 › Ed Sinek

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

tsql sql-server

Ed Sinek · 技术社区 · 14 年前

我有3个表代表“用户”、“角色”和多对多“用户角色”-带有键:userid、roleid;相关列:username、rolename

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

为用户列表使用一个select,然后为每个用户单独选择以获得角色是直接的,但是尝试构建一个输出下面内容的select,这让我很困惑。

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

事先谢谢,
——Ed

--编辑--
现在使用以下查询(感谢所有人,特别是Raymund& his blog ):

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 回复 | 直到 14 年前

John Raymund 13 年前

Converting / Parsing Rows to Delimited string column in SQL

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

OMG Ponies 14 年前

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

Dan J 14 年前

推荐文章

K.Z · 无法使用ADO获取Insert命令的记录Id。NET 4.0中的NET

2 年前

Duvan · 将逗号(,)替换为点(.)[副本]

2 年前

Yara1994 · 如果另一列具有特定值-SQL Server,则删除具有唯一Id的所有行

2 年前

Simon Worth · 是否可以使用SqlClient自动获取查询字符串中定义的参数列表

2 年前

Mateen Bagheri · 选择表的计数并选择其自身

2 年前

SoT · SQL Server中求和函数的工作方式

2 年前

rapha123 · 如何使用REPLACE函数使用SQL更改这两个单词?

2 年前

NKAT · 将列值聚合到列表中会产生错误

2 年前

deanpillow · 返回两列中有一个匹配值的记录

2 年前

snowflakes74 · 在Dapper中异步查询多个结果

2 年前