有用户表1 User
uid,uname;
角色用户表2 UserInRoles
uid,rolesid;
角色表 3 Roles
rolesid,rolesname;用户表与角色是多对多的关系;
能否用查询语句显示用户列表,显示如下效果(列出该用户所有角色名)用户id 用户名 角色
11 张三 角色1,角色2
uid,uname;
角色用户表2 UserInRoles
uid,rolesid;
角色表 3 Roles
rolesid,rolesname;用户表与角色是多对多的关系;
能否用查询语句显示用户列表,显示如下效果(列出该用户所有角色名)用户id 用户名 角色
11 张三 角色1,角色2
select rolesname from Roles
where rolesid in
(select b.uid, b.rolesid from UserInRoles a,User b where a.uid=b.uid and a.uname='张三')
已知用户ID的办法select * from Roles where rolesid in (select rolesid from UserInRoles where uid='11')
已知用户名的办法select * from Roles where rolesid in select (rolesid from UserInRoles inner join users on UserInRoles.uid=users.uid and user.Name='张三')
join UserInRoles on User.uid=UserInRoles.uid
join Roles on UserInRoles.rolesid=Roles.rolesid
where uname ='张三'
INSERT INTO @t DEFAULT VALUES
INSERT INTO @t DEFAULT VALUES
INSERT INTO @t DEFAULT VALUESdeclare @i varchar(max);
set @i='';
select @i=@i+','+CAST(ID AS VARCHAR(10)) from @t
select substring(@i,2,len(@i))
用户id 用户名 角色
11 张三 角色1,角色2 角色 是 叠加的
用 字查询 就好了
Create FUNCTION dbo.GetUserRoleNames
(
@uid int
) RETURNS varchar(200) AS
BEGIN declare @Str varchar(200), @roleName varchar(50)
select @Str='' declare role_cur cursor scroll for select
r.[Name]
from
[User] u inner join UserInRoles ur on ur.uid=u.uid
inner join Roles r on ur.rolesid=r.rolesid and u.id=@uid open role_cur fetch role_cur
into @roleName while @@fetch_status = 0
begin
select @Str=@Str + @roleName + ','
fetch next from role_cur
into @roleName
end close role_cur
deallocate role_cur
-- 返回值 return @Str
End-- 查询
select a.uid, a.uname, dbo.GetUserRoleNames(a.uid) as roleNames from [User] a