最近,在论坛中,遇到了不少比较难的sql问题,虽然自己都能解决,但发现过几天后,就记不起来了,也忘记解决的方法了。
所以,觉得有必要记录下来,这样以后再次碰到这类问题,也能从中获取解答的思路。
字符串检索问题:http://bbs.csdn.net/topics/390608926
这是 http://bbs.csdn.net/topics/390530288 问题的 一个变种
表
ID IndexArr
1 1,2,3,4,5
2 55,6,99,87,1000
3 7,567567,567,43,123
IndexArr 是","分割的数字
现在有字符串 '2,34,45,345,867,4,984'
现在要检索的是 IndexArr 中每一个数字都在 字符串中出现过的 结果集。
我的解法:
--1.函数
if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
drop function dbo.f_splitSTR
go
create function dbo.f_splitSTR
(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --分隔字符
)
returns @re table( --要返回的临时表
col varchar(1000) --临时表中的列
)
as
begin
declare @len int
set @len = LEN(@split) --分隔符不一定就是一个字符,可能是2个字符
while CHARINDEX(@split,@s) >0
begin
insert into @re
values(left(@s,charindex(@split,@s) - 1))
set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖:字符串以及分隔符
end
insert into @re values(@s)
return --返回临时表
end
go
--> 测试数据:[tb]
if object_id('[tb]') is not null
drop table [tb]
go
create table [tb]([ID] int,[IndexArr] varchar(19))
insert [tb]
select 1,'1,2,3,4,5' union all
select 2,'55,6,99,87,1000' union all
select 3,'7,567567,567,43,123' union ALL
SELECT 4,'2,34,45'
--------------开始查询--------------------------
DECLARE @s VARCHAR(1000)
SET @s= '2,34,45,345,867,4,984'
;with t
as
(
select t.ID,
t.IndexArr,
f.col,
--把IndexArr按照分隔符,拆分成了多少个字符串
COUNT(*) over(PARTITION by IndexArr) as split_str_count
from tb t
cross apply dbo.f_splitSTR(t.IndexArr,',') f
)
select t.ID,
t.IndexArr
from t
where charindex(col, ','+@s+',') > 0
group by t.ID,
t.IndexArr,
t.split_str_count
having COUNT(*) = t.split_str_count --比如2,34,45分拆为3个字符串,
--那么在经过where条件过滤后,记录数也必须是3
--这样说明了indexarr中的字符串都在@s变量中出现了