这样的查询如何写？

Question

select a.* 
from userids a ,
(select UserID from TSupplier where UserName like 'a')  b
where charindex(','+b.b+',',a.a)>0

Answer 1

select a.* 
from userids a ,
(select UserID from TSupplier where UserName like 'a')  b
where charindex(','+b.b+',',a.a)>0

Answer 2

select UserID from TSupplier where UserName like 'a' or Username like 'b'

Answer 3

create table T1(
UserIDS  varchar(400))
insert T1 select ',1,2,3,'
insert T1 select ',1,3,5,'
insert T1 select ',5,7,20,25,'
insert T1 select ',11,12,23,'
insert T1 select ',5,7,23,'
insert T1 select ',8,23,26,'
insert T1 select ',5,21,35,'
insert T1 select ',1,28,31,'
insert T1 select ',7,14,19,'
insert T1 select ',1,19,23,'declare @id varchar(400)
set @id = '1,23'
set @id = replace(@id, ',', ',%'' or userids like ''%,') 
set @id = 'like ''%,'+@id+',%'''
exec ('select * from T1 where userids '+@id)UserIDS                                                                                                                                                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
,1,2,3,
,1,3,5,
,11,12,23,
,5,7,23,
,8,23,26,
,1,28,31,
,1,19,23,

Answer 4

create table T1(
UserIDS  varchar(400))
insert T1 select ',1,2,3,'
insert T1 select ',1,3,5,'
insert T1 select ',5,7,20,25,'
insert T1 select ',11,12,23,'
insert T1 select ',5,7,23,'
insert T1 select ',8,23,26,'
insert T1 select ',5,21,35,'
insert T1 select ',1,28,31,'
insert T1 select ',7,14,19,'
insert T1 select ',1,19,23,'declare @id varchar(400)
set @id = '1,23'
set @id = replace(@id, ',', ',%'' or userids like ''%,') 
set @id = 'like ''%,'+@id+',%'''
exec ('select * from T1 where userids '+@id)UserIDS                                                                                                                                                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
,1,2,3,
,1,3,5,
,11,12,23,
,5,7,23,
,8,23,26,
,1,28,31,
,1,19,23,

Answer 5

create table T1(
UserIDS  varchar(400))
insert T1 select ',1,2,3,'
insert T1 select ',1,3,5,'
insert T1 select ',5,7,20,25,'
insert T1 select ',11,12,23,'
insert T1 select ',5,7,23,'
insert T1 select ',8,23,26,'
insert T1 select ',5,21,35,'
insert T1 select ',1,28,31,'
insert T1 select ',7,14,19,'
insert T1 select ',1,19,23,'declare @id varchar(400)
set @id = '1,23'
set @id = replace(@id, ',', ',%'' or userids like ''%,') 
set @id = 'like ''%,'+@id+',%'''
exec ('select * from T1 where userids '+@id)UserIDS                                                                                                                                                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
,1,2,3,
,1,3,5,
,11,12,23,
,5,7,23,
,8,23,26,
,1,28,31,
,1,19,23,

Answer 6

declare @str varchar(400)
set @str='1,3,5,2'    --假设@str是你要查询的字符串--用如下的语句查询就可以了.
select distinct a.* from t1 a,
(select cast(userid as varchar) as userid from T2 where charindex(','+cast(UserId as varchar)+',',','+@str+',')>0) b
where charindex(','+b.userid+',',a.UserIDS)>0

Answer 7

select a.* 
from userids a ,
(select UserID from TSupplier where UserName like 'a')  b
where charindex(','+b.b+',',a.a)>0有点小问题，修改后：
select * from T1 
where id in
(
  select distinct id
  from T1 a,
  (select UserID from T2 where UserName like 'a') b
  where charindex(','+cast(b.b as varchar(20))+',',a.a)>0
)谢谢楼上的各位朋友，非常感谢。

Answer 8

select * from T1 
where id in
(
  select distinct id
  from T1 a,
  (select UserID from T2 where UserName like 'a') b
  where charindex(','+cast(b.b as varchar(20))+',',a.a)>0
)可以简化为:select a.* from T1 a
left join 
  (select UserID from T2 where UserName like 'a') b
  on charindex(','+cast(b.b as varchar(20))+',' , a.a)>0