一个困扰多时的sql,求助
表tab中有以下记录：
id userids
1 11,15,21
2 11,23,45,12
3 55,66,77
我的sql语句中用到一个变量 ls_c，现ls_c= '11 ' ，现想检索tab中userids包含ls_c内容的记录。
请问怎么组合这一语句：
select * from tab where ls_c in (userids) ?
[解决办法]
select * from tab where charindex( ', '+ls_c+ ', ', ', '+userids+ ', ')> 0
[解决办法]
select * from tab where charindex( ', '+@ls_c+ ', ', ', '+userids+ ', ') > 0
[解决办法]
Select * from tab where Charindex( ', '+@ls_c+ ', ', ', '+userids+ ', ')> 0
[解决办法]
create table test (id int,userids varchar(100))
insert into test
select 1, '11,15,21 '
union all select 2, '11,23,45,12 '
union all select 3, '55,66,77 '
declare @ls_c varchar(20)
set @ls_c= '11 '
select *
from test
where userids like '% ' + @ls_c + '% '
drop table test
-------------------
id userids
111,15,21
211,23,45,12
（所影响的行数为 2 行）
[解决办法]
select * from tab where Charindex( ', '+@ls_c+ ', ', ', '+userids+ ', ') > 0
[解决办法]
select * from test where ( ', ' + userids + ', ') like ( '%, ' + @ls_c + ',% ')
楼上，我觉得得这样判断。
避免出现111，211这样的userid会影响like判断
[解决办法]
Declare @ls_c Varchar(10)
Set @ls_c= '11 '
---方法1
Select * From tab Where CharIndex( ', '+@ls_c+ ', ', ', '+userids+ ', ')> 0
---方法2
Select * From tab Where PatIndex( '%, '+@ls_c+ ',% ', ', '+userids+ ', ')> 0
---方法3
Select * From tab Where ', '+userids+ ', ' Like '%, '+@ls_c+ ',% '
[解决办法]
create table yang_kun (id int,userids varchar(100))
insert into yang_kun
select 1, '11,15,21 '
union all select 2, '11,23,45,12 '
union all select 3, '55,66,77 '
declare @ls_c varchar(20)
set @ls_c= '11 '
select * from yang_kun where Charindex( ', '+@ls_c+ ', ', ', '+userids+ ', ') > 0
-------------------------------------------
id userids
111,15,21
211,23,45,12
[解决办法]
select *
from test
where ', ' + userids + ', ' like '%, ' + @ls_c + ',% '
[解决办法]
---创建测试环境
Create Table tab (id int,userids varchar(100))
Insert Tab Select 1, '11,15,21 '
Union All Select 2, '11,23,45,12 '
Union All Select 3, '55,66,77 '
Select * From Tab
---查询结果
Declare @ls_c Varchar(10)
Set @ls_c= '11 '
---方法1
Select * From tab Where CharIndex( ', '+@ls_c+ ', ', ', '+userids+ ', ')> 0
---方法2
Select * From tab Where PatIndex( '%, '+@ls_c+ ',% ', ', '+userids+ ', ')> 0
---方法3
Select * From tab Where ', '+userids+ ', ' Like '%, '+@ls_c+ ',% '
---删除测试环境
Drop Table tab
--结果都为
/*
id userids
----------- ------------
1 11,15,21
2 11,23,45,12
（所影响的行数为 2 行）
*/
[解决办法]
select * from tab where Charindex( ', '+@ls_c+ ', ', ', '+userids+ ', ') > 0
[解决办法]
Select * From tab Where ', '+userids+ ', ' Like '%, '+@ls_c+ ',% '
[解决办法]
Select * From tab Where PatIndex( '%, '+@ls_c+ ',% ', ', '+userids+ ', ')> 0
[解决办法]
测试sql:
create table #tmp2(id int,userids varchar(200))
insert into #tmp2 select 1, '11,15,21 '
union all select 2, '11,23,45,12 '
union all select 3, '55,66,77 '
select * from #tmp2 where charindex( ', '+ '11 '+ ', ', ', '+userids+ ', ')> 0
结果:
id userids
--------------------
111,15,21
211,23,45,12
