某表有字段vList,其值如下所示:vList
2,43,56,84
34,82
8,12,62,94
4,7,9
76,78,98查询出包含如下格式的记录(1,2,3,4,5,6,7,8,9,10)很明显结果集有3条记录,分别是1、3、4问题:使用sql语句实现
2,43,56,84
34,82
8,12,62,94
4,7,9
76,78,98查询出包含如下格式的记录(1,2,3,4,5,6,7,8,9,10)很明显结果集有3条记录,分别是1、3、4问题:使用sql语句实现
调试欢乐多
不是包含如下格式
(1,2,3,4,5,6,7,8,9,10)是包含(1,2,3,4,5,6,7,8,9,10)中的任意一个值比如第一条记录中包含了 2
第三条记录中包含了8
第四条记录中包含了4 7 9也就是查询出(1,2,3,4,5,6,7,8,9,10)的值曾在字段vList中出现过的记录。
case when
charindex('1,',vList,0)>1
or charindex('2,',vList,0)>1
or charindex('3,',vList,0)>1
or charindex('4,',vList,0)>1
or charindex('5,',vList,0)>1
or charindex('6,',vList,0)>1
or charindex('7,',vList,0)>1
or charindex('8,',vList,0)>1
or charindex('9,',vList,0)>1
or charindex('10,',vList,0)>1
then 1 else 0 end = 1
select * from tb1 where
case when
charindex(',1,',','+vList+',')>0
or charindex(',2,',','+vList+',')>0
or charindex(',3,',','+vList+',')>0
or charindex(',4,',','+vList+',')>0
or charindex(',5,',','+vList+',')>0
or charindex(',6,',','+vList+',')>0
or charindex(',7,',','+vList+',')>0
or charindex(',8,',','+vList+',')>0
or charindex(',9,',','+vList+',')>0
or charindex(',10,',','+vList+',')>0
then 1 else 0 end = 1
1:不应该是大于1,因于等于0是在集合中没有找到字符。大于0就是在集合中找到了(其返回值是字符在集合中的下标)
2:没有对前后的临界值作处理,所以在列的前后加上符号‘,’,这样在是正确定位集合中出现过的字符。
(
vList varchar(200)
)insert into tt
select '1,2,3,4,5,6,7,8,9,10'
union all select '1,2,3,4,5,6,7,8,'
union all select '47,1,3,4,5,6,7,8,2,1'
union all select '1,21,2,3,11,4,5,6,7,8,9,10'
union all select '21,2,3,11,4,5,6,7,8,9,10'select * from ttselect vList from tt
where (vList like '1,%' or vList like '%,1,%' or vList like '%,1') and
(vList like '2,%' or vList like '%,2,%' or vList like '%,2') and
(vList like '3,%' or vList like '%,3,%' or vList like '%,3') and
(vList like '4,%' or vList like '%,4,%' or vList like '%,4') and
(vList like '5,%' or vList like '%,5,%' or vList like '%,5') and
(vList like '6,%' or vList like '%,6,%' or vList like '%,6') and
(vList like '7,%' or vList like '%,7,%' or vList like '%,7') and
(vList like '8,%' or vList like '%,8,%' or vList like '%,8') and
(vList like '9,%' or vList like '%,9,%' or vList like '%,9') and
(vList like '10,%' or vList like '%,10,%' or vList like '%,10') drop table tt