--例如: declare @t table(id int) insert into @t select 11 insert into @t select 44 insert into @t select 12 insert into @t select 4 insert into @t select 3 insert into @t select 1declare @n varchar(50) set @n='11,3,44' select * from @t where charindex(','+ltrim(id)+',',','+@n+',')>0
declare @str varchar(20) set @str = '77'select top 1 groupid from groups where charindex(','+ltrim(members)+',',','+@str+',')>0
--例如:
declare @t table(id int)
insert into @t select 11
insert into @t select 44
insert into @t select 12
insert into @t select 4
insert into @t select 3
insert into @t select 1declare @n varchar(50)
set @n='11,3,44'
select * from @t where charindex(','+ltrim(id)+',',','+@n+',')>0
set @str = '77'select top 1 groupid from groups where charindex(','+ltrim(members)+',',','+@str+',')>0
这一列存储了很多人的编号,为字符串数组。
比如:
小组[groupid ] 组名[groupName] 成员[members]
1 A 39, 84, 120
2 B 123, 143, 157, 151, 149, 116, 148, 108, 132
3 C 33, 125, 117, 32, 144, 163, 154
22 D 176, 83, 127, 172, 73, 170, 177, 162, 159
23 E 29, 169, 171
16 F 124, 155, 98, 126, 175, 174, 142, 131, 153, 113
18 G 30, 150, 166, 40
19 H 111, 145
20 I 167, 70, 141, 165, 152 未分组成员编号 77select members from groups where groupid = 22
执行结果为:176, 83, 127, 172, 73, 170, 177, 162, 159需求是根据成员编号来查询得到1个groupid,如果在分组中有的,则会正确查询出来;如果没有,比如77,会查询到177所在的小组。
原来的方法是 select top 1 groupid from groups where members like '%"& memers(n) &"%'
这样得到的结果是不正确的。改进后为 select top 1 groupid from groups where charindex(','+ltrim(members)+',',','+'"& memers(n) &"'+',')>0
这样查询不到任何数据。
怎么修改下呢?
select top 1 groupid from groups where charindex(','+'"& memers(n) &"'+',',','+ltrim(members)+',')>0