很有意思的SQL查询题目，看看大家如何解决

Question

这个也是我在一个论坛里看到的，好多人没有找到很好的解决方案。
数据库是SQL Server的

表名 Test 字段ID Str
数据
ID str
1 1,2,4,5,8,9
2 1,3,5,6,7,8,9,11
3 3,4,5,6,7
4 1,5,7,8,9
5 3,5,6,9,11,12
6 2,3,4,5,7
7 3,4,5,6,7,8
8 1,2,3,4,5,6,7,8,9,10
9 1,7,8,9

表里的数据就是类似这样的
现在问题就是 给出一个字符串 1,11,12 （长度不一定，也许是5个也许是6个）
找出含有这个字符串数字的所有数据
比如1,11,12 查询的结果应该是 ID: 1 2 4 5 8 9
含有1 11 12 的数据ID就是 1 2 4 5 8 9

如何用SQL语句实现，可以用程序处理
ID是不用考虑的，ID只是个标示
最大的问题就是charlif 说的 比如1、2、4和11、12、4虽然都是三位数字，但前者只有个位，后者含有十位和个位
如果查询1 有可能连11 12 牵扯进来

需要把逗号作为分割符来考虑,在查询的时候也把逗号加上就可以
了
这个办法也不错，可以尝试一下

把截出来的数字字符串转换成数值型的呢？
看来都是高手级的人物了

Answer 1

两个难点：
1、如何让Sql Server取到字符串中的数字，比如1、2、4和11、12、4虽然都是三位数字，但前者只有个位，后者含有十位和个位，用substring肯定是不行了。
2、如果取出了数字，还要依次遍历整张表，循环取ID，并去除重复ID

总得说来第一条智能取数比较折磨人。

标记一下，看有无高人进来
--------------------------------
仔细思考了下，还是做出来了，真的很折磨人。
下面的语句全部一下执行就可以看到结果了。
其中@strIn就是需要查找的字符串，如果要查看其他数据，修改这个值就可以了。
思路是把一个字段值变成多行数据，把','变为' union all'拼接语句实现。
--建立测试变量表@T，并赋值
declare @T table(id int,strnum varchar(50))
insert into @T
select 1,'1,2,4,5,8,9'
union all
select 2,'1,3,5,6,7,8,9,11'
union all
select 3,'3,4,5,6,7'
union all
select 4,'1,5,7,8,9'
union all
select 5,'3,5,6,9,11,12'
union all
select 6,'2,3,4,5,7'
union all
select 7,'3,4,5,6,7,8'
union all
select 8,'1,2,3,4,5,6,7,8,9,10'
union all
select 9,'1,7,8,9'
--@strIn查找的字符串
declare @strIn varchar(1000)
set @strIn = '1,11,12'

declare @strtable table(strSql varchar(4000))
insert into @strtable
Select 'select '''+cast(id as varchar(3))+''' as id,'''+replace(strnum,',',''' as num Union all Select ' + ''''+cast(id as varchar(3))+''' as id, ''') + ''' as num'
from @T

declare @comtable table(strSql varchar(4000))
insert into @comtable
select 'select '''+replace(@strIn,',',''' as num Union all Select ''' )+ ''' as num'

declare @textsql varchar(4000)
set @textsql = ''
Select @textsql=@textsql+strSql+' Union all ' from @strtable
set @textsql = substring(@textsql,1,len(@textsql)-10)

declare @comsql varchar(4000)
set @comsql = ''
Select @comsql=@comsql+strSql+' Union all ' from @comtable
set @comsql = substring(@comsql,1,len(@comsql)-10)

set @textsql = 'declare @Rtexttable table(id int,num varchar(3)) insert into @Rtexttable '+@textsql+
' declare @Rcomtable table(num varchar(3)) insert into @Rcomtable '+@comsql+' select distinct id from @Rtexttable a join @Rcomtable b on a.num = b.num'
exec (@textsql)

-------------------------
看了WHITE_WIN的，醍醐灌顶，豁然开朗。本人不喜欢用函数就改成存储过程了
declare @T table(id int,strnum varchar(50))
insert into @T
select 1,'1,2,4,5,8,9'
union all
select 2,'1,3,5,6,7,8,9,11'
union all
select 3,'3,4,5,6,7'
union all
select 4,'1,5,7,8,9'
union all
select 5,'3,5,6,9,11,12'
union all
select 6,'2,3,4,5,7'
union all
select 7,'3,4,5,6,7,8'
union all
select 8,'1,2,3,4,5,6,7,8,9,10'
union all
select 9,'1,7,8,9'

declare @strIn varchar(100)
set @strIn = '1,11,12'

declare @RT table (strnum varchar(30))

while charindex(',',@strIn)>0
begin
insert into @RT select left(@strIn,charindex(',',@strIn)-1)
set @strIn = substring(@strIn,charindex(',',@strIn)+1,len(@strIn))
end
If(len(@strIn)>0)
begin
insert into @RT select @strIn
end

select a.id from @T a, @RT b where ','+a.strnum+',' like '%,'+b.strnum+',%'
group by a.id
order by a.id

Answer 2

看标准答案，一句解决问题！

SELECT ID FROM TEST A,DBO.MYSPLICT('1,11,12',',') B WHERE ','+A.[STR]+',' LIKE '%,'+B.STRN+',%'

查出的字串换成数值型
SELECT ID,cast(strn as int) strn FROM TEST A,DBO.MYSPLICT('1,11,12',',') B WHERE ','+A.[STR]+',' LIKE '%,'+B.STRN+',%'

这个问题一定是什么考题，用来考你的表值函数的运用。

所以要建立一个我们会经常用到的返回表值的函数。
即：将按某分隔符分隔的格式字串拆分开（解码），然后每一子串成为返回的表中的一个记录，就是下面的函数，建议收入自已的自定义函数收藏，以方便调用：

CREATE FUNCTION MYSPLICT
(@str nvarchar(2000),--要分割的字符串
@Deli varchar(1))--分隔符
RETURNS @table table(strn varchar(30))--反回一个子串表
AS
BEGIN
while charindex(@deli,@str)>0
begin
insert into @table select left(@str,charindex(@deli,@str)-1)
set @str=substring(@str,charindex(@deli,@str)+1,len(@str))
end
if len(@str)>0
insert into @table select @str
return
END
GO本回答被提问者采纳

Answer 3

呵呵，是比较有意思，
关键应该是截取分隔字符串的问题，
1.将目标字符串进行分隔，得到具体的几个数字，
2.对目标字符串中的每一个数字进行以下的操作，
（1）.对记录重点str字段也进行分隔字符串，获得其中的数字，
（2）.对目标字符串中的数字在str字段中的多个数字中进行查找
（3）.如果找到，则返回str字段记录对应的id标号，
（4）.如果没找到，就继续下一个循环
这样最后应该就可以得到最后需要的结果，可能具体做起来还有不少细节需要考虑，大家多交流，^_^

Answer 4

楼上的办法就可以..

你要查询的字符串在程序当中处理为一个数组,然后分别查出结果,最后来取这些结果的并集就行了

你可以把这张表的数据整理一下放入第三张表

第三张表只记录那一行包括了哪些数字,这样效率会高很多

ID str
1 1,2,4,5,8,9
2 1,3,5,6,7,8,9,11
3 3,4,5,6,7
4 1,5,7,8,9
5 3,5,6,9,11,12
6 2,3,4,5,7
7 3,4,5,6,7,8
8 1,2,3,4,5,6,7,8,9,10
9 1,7,8,9

第三张表 c
strid num
1 1
1 2
1 4
1 5
1 8
1 9
2 1
2 3
2 5
2 7
2 8
2 9
2 11

这样你就可以这样查了

select distinct id
from c
where num in(字符串)

Answer 5

create database test
use test

create table test
(
id int identity(1,1),
tes varchar(100)
)

insert into test values('1,2,4,5,8,9')
insert into test values('1,3,5,6,7,8,9,11')
insert into test values('3,4,5,6,7')
insert into test values('1,5,7,8,9')
insert into test values('3,5,6,9,11,12')
insert into test values('2,3,4,5,7')
insert into test values('3,4,5,6,7,8')
insert into test values('1,2,3,4,5,6,7,8,9,10')
insert into test values('1,7,8,9')

我的答案不对~
但数据我都做出来了，大家可以继续试验。

我现在只能队个位数的解决了~但两位的还没解决

select * from test where tes like '[9]%' or tes like '%[9]' or tes like '%[9]%'

如果是用在论坛，看拥有某个权限的用户有多少人，这个就足够了~但题面的答案还有一点距离。

我还能提供一点思路：

字符串是字符型的~~我们能不能想办法把截出来的数字字符串转换成数值型的呢？这样比较就一定是准确的了。

x------------------

现在有了一个很笨的方法：

下面的是查询11和12还有3的

select * from test where (tes like '[3]%' or tes like '%[3]' or tes like '%[3]%') and (tes like '1[1]%' or tes like '%1[1]' or tes like '%1[2]%') and (tes like '1[2]%' or tes like '%1[2]' or tes like '%1[2]%')

这个方法是把10以内的分成一组~10-19分成一组。。。以此类推~~

虽然笨点，但结果还是出来了。