問題描述
我有 3 個(gè)表,分別代表Users"、Roles"和多對(duì)多的UsersInRoles"——鍵為:UserId、RoleId;相關(guān)列: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 應(yīng)用程序中,我想顯示所有用戶及其所在角色的列表.我試圖從 SQL 構(gòu)建一個(gè)將返回此信息的查詢.應(yīng)該對(duì)角色列表進(jìn)行分隔,以便我可以進(jìn)行適當(dāng)?shù)难菔静僮?取決于演示平臺(tái),例如用 BR 標(biāo)記替換分隔符).
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).
對(duì)用戶列表使用一個(gè)選擇,然后為每個(gè)用戶單獨(dú)選擇來獲取角色是很簡單的,但是嘗試構(gòu)建一個(gè)輸出以下內(nèi)容的選擇讓我感到困惑.
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
--編輯--
現(xiàn)在使用以下查詢(感謝所有人,特別是 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
推薦答案
這是您的解決方案:
轉(zhuǎn)換/將行解析為 SQL 中的分隔字符串列
編輯
如果您需要進(jìn)一步澄清,這里是答案
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
這篇關(guān)于SQL:從單個(gè)查詢中列出多對(duì)多的文章就介紹到這了,希望我們推薦的答案對(duì)大家有所幫助,也希望大家多多支持html5模板網(wǎng)!