主表A用户表，子表B角色关联表。用户可能存在多个角色。
即：UID RID
1 1
1 2
2 1
3 3
2 3
怎么实现查询用户表，显示用户角色字段是多个对应的角色啊?
（一般1对1的角色到好办，直接多表查询，select a.name,a.age, c.rolename......... from ..... where A.uid= B.uid and B.rid=c.rid ）
------解决方案--------------------
select a.UID, b.Name,c.RoleName
from a inner join b
on a.UID=b.UID inner join c
on b.RID=c.RID
go
然后写个函数实现行转列就可以了
create function fn_AA(@Field NVARCHAR(10))
returns nvarchar(100)
as
begin
DECLARE @A NVARCHAR(100)
set @A= ' '
SELECT @[email protected]+c.RoleName+ '， '
from a inner join b on a.UID=b.UID
inner join c on b.RID=c.RID
WHERE [email protected]
RETURN @a
end
GO
select a.UID, b.Name,dbo.fn_AA(a.UID) as RoleName
from a inner join b
on a.UID=b.UID inner join c
on b.RID=c.RID