求一SQL语句？请教哥们
UserId UserName RoleID
1 qqq A,B
RoleID Role
A 1,0,0,1,1
B 0,1,0,1,1
C 1,1,1,1,1
如果用户兼俩个角色 A B
请问怎么构造查询语句得到用户名为qqq的权限集合为1，1，0，1，1
谢谢
[解决办法]
如果 role的长度固定，可以用一句:
declare @a table(UserId int, UserName varchar(10), RoleID varchar(20))
insert @a select 1 , 'qqq ', 'A,B '
insert @a select 2 , 'ggg ', 'C,A,B '
declare @b table(RoleID varchar(10), Role varchar(20))
insert @b select 'A ', '1,0,0,1,1 '
union all select 'B ' , '0,1,0,1,1 '
union all select 'C ' , '1,1,1,1,1 '
select
username,
case when substring(x,1,1)> '0 ' then '1, ' else '0, ' end+
case when substring(x,2,1)> '0 ' then '1, ' else '0, ' end+
case when substring(x,3,1)> '0 ' then '1, ' else '0, ' end+
case when substring(x,4,1)> '0 ' then '1, ' else '0, ' end+
case when substring(x,5,1)> '0 ' then '1 ' else '0 ' end role
from(
select ltrim(sum(cast(replace(role, ', ', ' ') as int))) x,username from @b b,@a a where charindex( ', '+b.roleid+ ', ', ', '+a.roleid+ ', ')> 0 group by username
)bb
[解决办法]
--result
/*
username role
---------- ---------
ggg 1,1,1,1,1
qqq 1,1,0,1,1
（所影响的行数为 2 行）
*/
[解决办法]
create table a(userID int,username varchar(10),roleID varchar(10))
insert into a select 1, 'qqq ', 'A,B '
insert into a select 2 , 'ggg ', 'A,,C '
create table b(roleID varchar(10),role varchar(10))
insert into b
select 'A ', '1,0,0,1,1 ' union all
select 'B ', '0,1,0,1,1 ' union all
select 'C ', '1,0,1,1,0 '
select username,
stuff(
case when sum(convert(int,substring(role,1,1)))> 0 then ',1 ' else ',0 ' end+
case when sum(convert(int,substring(role,2,1)))> 0 then ',1 ' else ',0 ' end+
case when sum(convert(int,substring(role,3,1)))> 0 then ',1 ' else ',0 ' end+
case when sum(convert(int,substring(role,4,1)))> 0 then ',1 ' else ',0 ' end+
case when sum(convert(int,substring(role,5,1)))> 0 then ',1 ' else ',0 ' end,1,1, ' ') AS role
from (
select a.userID,a.username,replace(role, ', ', ' ') as role
from a inner join b
on charindex( ', '+b.roleID+ ', ', ', '+a.roleID+ ', ')> 0) T
group by username
/*
username role
---------- ---------
ggg 1,0,1,1,1
qqq 1,1,0,1,1
*/
drop table a,b
[解决办法]
sum的部份,可能放外面判斷比較好,不然可能超過10個1的話,會進位
------解决方案--------------------
create table t_User (UserId int,UserName varchar(20),RoleID varchar(50))
go
create table t_Role (RoleID varchar(10),[Role]varchar(50))
go
insert into t_User
select 1, 'qqq ', 'A,B '
union all select 1, 'ppp ', 'C,B '
union all select 1, 'aaa ', 'A,C '
union all select 1, 'bbb ', 'A,B,C '
insert into t_Role
select 'A ', '1,0,0,1,1 '
union all select 'B ', '0,1,0,1,1 '
union all select 'C ', '1,1,1,1,1 '
go
if exists(select name from sysobjects where name= 'fn_GetUserRoles 'and type= 'FN ')
drop function [dbo].[fn_GetUserRoles]
go
create function [dbo].[fn_GetUserRoles]
(
@pRoleID varchar(50)
)
returns varchar(50)
as
begin
declare @v_len int
declare @v_Role varchar(50)
select @v_len=max(len(replace([Role], ', ', ' '))) from t_Role where charindex(RoleID,@pRoleID)> 0
set @v_Role= ' '
while @v_len> 0
begin
if exists(select 1 from t_Role where charindex(RoleID,@pRoleID)> 0 and
substring(replace([Role], ', ', ' '),@v_len,1)= '1 ')
begin
set @v_Role=@v_Role+ '1, '
end
else
begin
set @v_Role=@v_Role+ '0, '
end
set @v_len=@v_len-1
end
set @v_Role=left(@v_Role,len(@v_Role)-1)
return @v_Role
end
go
select UserId,UserName,RoleID,[dbo].[fn_GetUserRoles](RoleID)as Roles from t_User
drop table t_User
drop table t_Role
go
/*结果
UserId UserName RoleID Roles
----------- ----------------- ---------------- ----------------------
1 qqq A,B 1,1,0,1,1
1 ppp C,B 1,1,1,1,1
1 aaa A,C 1,1,1,1,1
1 bbb A,B,C 1,1,1,1,1
*/
[解决办法]
我的这个是对付role可能不止5个的情况，当然，如果每个RoleID对应的Role如果个数不相同的话上面那段代码得再修改一下，不过应该不会有这种情况吧~_~
[解决办法]
create table a(userID int,username varchar(10),roleID varchar(10))
insert into a select 1, 'qqq ', 'A,B '
insert into a select 2 , 'ggg ', 'A,C '
create table b(roleID varchar(10),role varchar(10))
insert into b
select 'A ', '1,0,0,1,1 ' union all
select 'B ', '0,1,0,1,1 ' union all
select 'C ', '1,0,1,1,0 '
select a.username,
max(substring(b.role,1,1))+ ', '+
max(substring(b.role,3,1))+ ', '+
max(substring(b.role,5,1))+ ', '+
max(substring(b.role,7,1))+ ', '+
max(substring(b.role,9,1))[role]
from
a join b
on charindex(b.roleid,a.roleid)> 0
where a.username= 'qqq '
group by a.username
username role
---------- --------------
qqq 1,1,0,1,1
（所影响的行数为 1 行）
[解决办法]
这招是wgzaaa() 的~~HOHO~~~
http://community.csdn.net/Expert/topic/5575/5575157.xml?temp=.9108087
