求助：两个用逗号分隔的字符串比较是否包含？

[coolbel2000]

各位高人好！

有这样一个场景:
用户表A，其中角色字段role_id有一个或多个角色ID用逗号分隔，如：1,3
表B，权限资源表，其中，role_popedom为那些角色拥有权限，也是用逗号分隔，如：1,5,7,8,11,13

表A
role_id
1
1,3
2,3
5,13

表B

role_popedom
1,3,5,11,13,15
2,3,5,12,16,17,18
5,13


现在问题是，如何根据用一个用户的角色role_id,来查询这个用户拥有那些权限？也就是说表B中role_popedom字段中包含role_id字段。
举例：
如果用户的角色为role_id=2,3 那么拥有的权限则有两条
1,3,5,11,13,15
2,3,5,12,16,17,18

请高人指点，谢谢！

[coolbel2000]

先谢 cd731107 了！

看不太懂，先消化一下，回头结贴，谢谢！

[快溜]

declare @str varchar(10)
set @str='2,3'
select * from 表B where patindex('%['+@str+']%',role_popedom)>0

[coolbel2000]

TO：cd731107

发现一个问题：

如果role_id 是一个位数的话，查询结果不对，如下：
当role_id = 1时，应该能查到1条记录，但结果为空。
当role_id = 3时，应该能查到2条记录，但结果为空。

select b.role_popedom from a,b,master..spt_values
where type='p' and charindex(',',','+role_id,number)=number
and charindex(','+substring(role_id,number,charindex(',',role_id+',',number)-number)+',',role_popedom)>0
and role_id='3'

[coolbel2000]

TO：ssp2009

这样有问题，如果role_id = 2,3 时，

declare @str varchar(10)
set @str='2,3'
select * from B where patindex('%['+@str+']%',role_popedom)>0

这样的话，就查出来3条记录，第三条记录不符合。

1,3,5,11,13,15
2,3,5,12,16,17,18
5,13

[AcHerat]

create table A(role_id varchar(10))
insert into A
select '1' union all
select '1,3' union all
select '2,3' union all
select '5,13' union all
select '3'    --添加3的信息
go

create table B(role_popedom varchar(100))
insert into B
select '1,3,5,11,13,15' union all
select '2,3,5,12,16,17,18' union all
select '5,13'
go

;with ach as
(
    select a.rid,a.role_id,substring(a.role_id,b.number,charindex(',',a.role_id+',',b.number) - b.number) role_id1
    from (select rid=row_number() over (order by getdate()),role_id from A) a,master..spt_values b
    where b.[type] = 'p' and b.number between 1 and len(a.role_id)
        and substring(','+a.role_id,b.number,1) = ','
)

select distinct b.*
from B b,ach t
where charindex(','+t.role_id1+',',','+b.role_popedom+',') > 0 and t.role_id = '3'

/*
为1的时候

role_popedom
----------------------------------------------------------------------------------------------------
1,3,5,11,13,15

(1 行受影响)
*/

/*
为3的时候

role_popedom
----------------------------------------------------------------------------------------------------
1,3,5,11,13,15
2,3,5,12,16,17,18

(2 行受影响)
*/

drop table A,B

[cd731107]

select b.role_popedom from a,b,master..spt_values
where type='p' and charindex(',',','+role_id,number)=number 
and charindex(','+substring(role_id,number,charindex(',',role_id+',',number)-number)+',',','+role_popedom+',')>0
and role_id='1'

/*
role_popedom                                                                                         
------------------
1,3,5,11,13,15
*/

[cd731107]

--上面的不要，用这个

select distinct b.role_popedom from a,b,master..spt_values
where type='p' and charindex(',',','+role_id,number)=number 
and charindex(','+substring(role_id,number,charindex(',',role_id+',',number)-number)+',',','+role_popedom+',')>0
and role_id='1'

/*
role_popedom                                                                                         
------------------
1,3,5,11,13,15
*/

[快溜]

[Quote=引用 7 楼 coolbel2000 的回复:]
TO：ssp2009

这样有问题，如果role_id = 2,3 时，

declare @str varchar(10)
set @str='2,3'
select * from B where patindex('%['+@str+']%',role_popedom)>0

这样的话，就查出来3条记录，第三条记录不符合。

1,3,5,11,13,15
2,3,5,12……
[/Quote]

declare @str varchar(10)
set @str='2,3'
select * from B where patindex('['+@str+']',role_popedom)>0

[yyoinge]

建立一个函数，用来检查两个字符串之间是否有重复的项目（项目间以,分隔），这样通用一些

Create function fn_check_repeat(@bm1 varchar(2000), @bm2 varchar(2000))
returns tinyint
-- 检查两个字符串中是否有重复的单项
-- 如 '001,002' 和 '002,003' 返回1
--	 '001,002' 和 '004,003' 返回0
	as begin
		declare @p1 int
		declare @p2 int
		declare @bm varchar(2000)
		set @p1 = 0
		set @p2 = 0
		set @bm = ''
		set @bm1 = isnull(@bm1, '') 
		set @bm2 = isnull(@bm2, '') 
		if @bm1 = '' --空代表全部，则返回匹配 
			return 1
		if @bm2 = '' -- 空代表全部，则返回匹配 
			return 1
		-- @bm1转换格式为',001,002,003,'
		if left(@bm1, 1) <> ','
			set @bm1 = ',' + @bm1 
		if right(@bm1, 1) <> ','
			set @bm1 = @bm1 + ','
		-- @bm2转换格式为'001,002,003,'
		if left(@bm2, 1) = ','
			set @bm2 = substring(@bm2, 2, 255)
		if right(@bm2, 1) <> ','
			set @bm2 = @bm2 + ','
		select @p1 = CHARINDEX(',', @bm2)
		while(@p1 <> 1 and @p1 <> 0)
		begin
			set @bm = left(@bm2, @p1 - 1)
			if  CHARINDEX(',' + @bm + ',', @bm1) > 0
				return 1
			set @bm2 = substring(@bm2, @p1 + 1, 255)
			select @p1 = CHARINDEX( ',', @bm2)
		end

		return 0
end

使用时，这样既可

select * from 表A a, 表B b where dbo.fn_check_repeat(a.role_id, b.role_popedom) = 1

其他表需要用的时候也可以直接使用该函数

[coolbel2000]

谢谢各位！结贴。

觉得yyoinge用函数形式比较方便和通用。

[AcHerat]

那如果是5,13 是不是 就是三条！

[AcHerat]

create table A(role_id varchar(10))
insert into A
select '1' union all
select '1,3' union all
select '2,3' union all
select '5,13'
go

create table B(role_popedom varchar(100))
insert into B
select '1,3,5,11,13,15' union all
select '2,3,5,12,16,17,18' union all
select '5,13'
go

;with ach as
(
	select a.rid,a.role_id,substring(a.role_id,b.number,charindex(',',a.role_id+',',b.number) - b.number) role_id1
	from (select rid=row_number() over (order by getdate()),role_id from A) a,master..spt_values b
	where b.[type] = 'p' and b.number between 1 and len(a.role_id)
		and substring(','+a.role_id,b.number,1) = ','
)

select distinct b.*
from B b,ach t
where charindex(','+t.role_id1+',',','+b.role_popedom+',') > 0 and t.role_id = '2,3'

drop table A,B

/*************************

role_popedom
----------------------------------------------------------------------------------------------------
1,3,5,11,13,15
2,3,5,12,16,17,18

(2 行受影响)

[cd731107]

create table A(role_id varchar(10))
insert into A
select '1' union all
select '1,3' union all
select '2,3' union all
select '5,13'

create table B(role_popedom varchar(100))
insert into B
select '1,3,5,11,13,15' union all
select '2,3,5,12,16,17,18' union all
select '5,13'

select b.role_popedom from a,b,master..spt_values
where type='p' and charindex(',',','+role_id,number)=number 
and charindex(','+substring(role_id,number,charindex(',',role_id+',',number)-number)+',',role_popedom)>0
and role_id='2,3'

drop table a,b

/*
role_popedom          
----------------------
1,3,5,11,13,15
2,3,5,12,16,17,18

（所影响的行数为 2 行）
*/