DECLARE @t TABLE(toUSER NVARCHAR(10)) INSERT INTO @t SELECT 'zs' UNION ALL SELECT 'ls' UNION ALL SELECT 'ww' UNION ALL SELECT 'ww' UNION ALL SELECT 'zs' UNION ALL SELECT 'wt,aa' UNION ALL SELECT 'aa,bb,cc,'SELECT * FROM @t WHERE PATINDEX('%aa%',toUSER)>0 OR PATINDEX('%bb,cc%',toUSER)>0
select * from ta where patindex('%'+ToUser+'%',',ls,')>0 or patindex('%'+ToUser+'%',',zs,')>0
--反了 select * from ta where patindex('%,ls,%',ToUser)>0 or patindex('%,zs,%',ToUser)>0
我用过CHARINDEX函数 select * from ta CHARINDEX(ToUser,',ls,zs,') 但是只得一条记录
---> 测试数据: a create table a(Id int,ToUser varchar(20)) insert into a select 1,',zs,ls,' union all select 2,',zs,' union all select 3,',ww,' union all select 4,',aa,bb,cc' union all select 5,',bb,ls,' declare @s varchar(500) set @s=',zs,ls,' select @s = replace(@s,',',''' v union all select ''') set @s=substring(@s,14,len(@s)-14-17) exec('select distinct a.* from a ,('+@s+')b where charindex('',''+b.v+'','',a.ToUser)>0')
try:declare @names varchar(1000) set @names=',zs,ls,' set @names=stuff(@names,1,1,'') select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b select distinct a.Id,a.ToUser from 表A a ,( select username=substring(left(@names,len(@names)-1),b.ID,charindex(',',@names,b.ID)-b.ID) from #Num b where charindex(',',','+left(@names,len(@names)-1),b.ID)=b.ID ) c where charindex(','+b.username+',',a.ToUser)>0
select UserId into tempUser from Users where CHARINDEX(DepartmentId,'10001,10024,10020,')>0 select * from [Document],temp where CharIndex(temp.UserId,[Document].ToUser)>0
写错了个小地方……declare @names varchar(1000) set @names=',zs,ls,' set @names=stuff(@names,1,1,'') select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b select distinct a.Id,a.ToUser from 表A a ,( select username=substring(left(@names,len(@names)-1),b.ID,charindex(',',@names,b.ID)-b.ID) from #Num b where charindex(',',','+left(@names,len(@names)-1),b.ID)=b.ID ) c where charindex(','+c.username+',',a.ToUser)>0
搞定了。要用户放到一个临时表temp select * from tab,temp where CharIndex(temp.UserId,tab.ToUser)>0
没有规律,没有临时表.就这个整咯.
最好把表结构给出来。。
表A:
Id ToUser
1 ,zs,ls,
2 ,zs,
3 ,ww,
4 ,aa,bb,cc,
5 ,bb,ls,
....现在要获取zs,ls的数据。给你一个串如 ,zs,ls,
得到的结果应该是
Id ToUser
1 ,zs,ls,
2 ,zs,
5 ,bb,ls,
DECLARE @t TABLE(toUSER NVARCHAR(10))
INSERT INTO @t
SELECT 'zs' UNION ALL
SELECT 'ls' UNION ALL
SELECT 'ww' UNION ALL
SELECT 'ww' UNION ALL
SELECT 'zs' UNION ALL
SELECT 'wt,aa' UNION ALL
SELECT 'aa,bb,cc,'SELECT * FROM @t WHERE PATINDEX('%aa%',toUSER)>0 OR PATINDEX('%bb,cc%',toUSER)>0
select * from ta where patindex('%,ls,%',ToUser)>0 or patindex('%,zs,%',ToUser)>0
这个中方案中,如果给的串是,zs,ls,aa,bb,
那不是要四个select ?
insert @t select 1, ',zs,ls,'
insert @t select 2, ',zs,'
insert @t select 3, ',ww,'
insert @t select 4, ',aa,bb,cc,'
insert @t select 5, ',bb,ls,' select * from @t where patindex('%,ls,%',ToUser)>0 or patindex('%,zs,%',ToUser)>0
/*
Id ToUser
----------- --------------------
1 ,zs,ls,
2 ,zs,
5 ,bb,ls,*/
select * from ta CHARINDEX(ToUser,',ls,zs,')
但是只得一条记录
create table a(Id int,ToUser varchar(20))
insert into a
select 1,',zs,ls,' union all
select 2,',zs,' union all
select 3,',ww,' union all
select 4,',aa,bb,cc' union all
select 5,',bb,ls,'
declare @s varchar(500)
set @s=',zs,ls,'
select @s = replace(@s,',',''' v union all select ''')
set @s=substring(@s,14,len(@s)-14-17)
exec('select distinct a.* from a ,('+@s+')b where charindex('',''+b.v+'','',a.ToUser)>0')
set @names=',zs,ls,'
set @names=stuff(@names,1,1,'')
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
select distinct a.Id,a.ToUser from 表A a
,(
select username=substring(left(@names,len(@names)-1),b.ID,charindex(',',@names,b.ID)-b.ID)
from #Num b
where charindex(',',','+left(@names,len(@names)-1),b.ID)=b.ID
) c
where charindex(','+b.username+',',a.ToUser)>0
select * from [Document],temp where CharIndex(temp.UserId,[Document].ToUser)>0
set @names=',zs,ls,'
set @names=stuff(@names,1,1,'')
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
select distinct a.Id,a.ToUser from 表A a
,(
select username=substring(left(@names,len(@names)-1),b.ID,charindex(',',@names,b.ID)-b.ID)
from #Num b
where charindex(',',','+left(@names,len(@names)-1),b.ID)=b.ID
) c
where charindex(','+c.username+',',a.ToUser)>0
select * from tab,temp where CharIndex(temp.UserId,tab.ToUser)>0