有两个表,
Test_User
UserId,int
UserName nvarchar(50)
Test_User_Role
UserId int
RoleId int
一个UserId对应多个RoleId
取出这样的记录
UserId RoleIDs
1 1,2
2 2
3 1,2,3
Sql
Select UserId,stuff((select ','+cast(RoleId as nvarchar(50)) from Test_User_Role where
Test_User.UserId = Test_User_Role.UserId for xml path ('')),1,1,'') as RoleIds
from Test_User
where 1=1
stuff(express1,startIndex,lengthIndex,express2)
他的作用是把express1从startIndex位置,删除lengthIndex个字符,然后把express2插入到startIndex,
select( ','+cast(RoleId as nvarchar(50)) from Test_User_Role whereTest_User.UserId = Test_User_RoleId for xml path ('') 取出来的是“,1,2”,stuff作用是把第一个“,”去掉