求一个匹配拼接字符串的SQL

[dugupiaoyun]

如参数tag值为1,3,4
如何用效率高的方式查出匹配表A的字段Tags的记录
ID Tags
-------------------------------------
1 1,5,9,13,34
2 3,4,7,14,41
3 2,3,1,5,4
4 11,3,4,9,6
5 11,1,3,4,8

查询结果显示
ID Tags
-------------------------------------
3 2,3,1,5,4
5 11,1,3,4,8

[dugupiaoyun]

注意：参数tag为变量，不确定字符串长度的

[不穿内裤的超人]

with cte(id,tags) as (
select 1,                   '1,5,9,13,34'union 
select 2,                   '3,4,7,14,41'union
select 3,                  '2,3,1,5,4'union
select 4,                   '11,3,4,9,6'union
select 5,                   '11,1,3,4,8')
select * from cte where tags like (select dbo.[m_fuzzyquery_v1]('1,3,4'))

1	1,5,9,13,34
4	11,3,4,9,6
5	11,1,3,4,8

这是模糊查询，貌似还有点不合适 函数如下：

create function [dbo].[m_fuzzyquery_v1]
(
    @str nvarchar(2000)
)  
returns nvarchar(2000)
as  
begin  
       declare @count int,@i int;
       declare @newchar nvarchar(200),@nn nvarchar(300),@hh nvarchar(200)
       set @count=len(@str);set @i=1;set @nn='';
       while @i<@count+1
       begin
           set @newchar=substring(@str,@i,1)+'%'
           set @nn=@nn+@newchar;
           set @i=@i+1;
       end
    set @hh='%'+@nn
    return @hh
end

[道素]

做这个参考

DECLARE @tag VARCHAR(100)='1,3,4'
DECLARE @tagxml XML 
SET @tagxml='<tags><tag>'+REPLACE(@tag,',','</tag><tag>')+'</tag></tags>'

;with cte(id,tags) as (
select 1,                   '1,5,9,13,34'union 
select 2,                   '3,4,7,14,41'union
select 3,                  '2,3,1,5,4'union
select 4,                   '11,3,4,9,6'union
select 5,                   '11,1,3,4,8')
select cte.id,cte.tags from cte
INNER JOIN (
	SELECT b.value('.','varchar(max)') AS tag,COUNT(0)OVER(PARTITION BY 0 ) AS tagcount FROM @tagxml.nodes('tags/tag') AS s(b)
) AS t ON 1=1--CHARINDEX(','+t.tag+',',cte.tags)>0
GROUP BY cte.id,cte.tags,t.tagcount
HAVING sum(CASE WHEN  CHARINDEX(','+t.tag+',',','+cte.tags+',')>0 THEN 1 ELSE 0 END)=t.tagcount

[中国风]

参照方法： http://blog.csdn.net/roy_88/article/details/1387459 这样的比较最好用CLR写函数去匹配，或用全文检索

[dugupiaoyun]

没办法，最后还是自己写了个函数来处理解决这个。