原问题
由于昨天一些事，没有测试完全，
，又来请教大牛们了，菜鸟谢过了。SELECT * FROM table WHERE CHARINDEX(','+参数+',',','+列名+',')>0上面这句话当参数是 0,1,2,3,4,5...的时候没有问题，但是当测试的是 0_1,0_1_2....这两类数据的时候，就出现错误了。
------解决方案--------------------
select replace(' 0_1,0_1_2','_',',')先把数据替换掉
------解决方案--------------------
-- test1
declare @x varchar(100)
select @x='0,1,1,2,2,3,4,3,4'
select cast(s as varchar(20)) 's',count(1) '出现次数'
from
(select substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) 's'
from (select @x 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(','+a.s,b.number,1)=',') t
group by s
/*
s 出现次数
---------- -----------
0 1
1 2
2 2
3 2
4 2
(5 row(s) affected)
*/
-- test2
declare @x varchar(100)
select @x='0_1,0_2,0_3,1_2,1_3,1_3,1_2'
select cast(s as varchar(20)) 's',count(1) '出现次数'
from
(select substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) 's'
from (select @x 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(','+a.s,b.number,1)=',') t
group by s
/*
s 出现次数
---------- -----------
0_1 1
0_2 1
0_3 1
1_2 2
1_3 2
(5 row(s) affected)
*/
-- test3
declare @x varchar(100)
select @x='0_0_0,0_1_1,0_1_2,0_1_1,0_2_2'
select cast(s as varchar(20)) 's',count(1) '出现次数'
from
(select substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) 's'
from (select @x 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(','+a.s,b.number,1)=',') t
group by s
/*
s 出现次数
---------- -----------
0_0_0 1
0_1_1 2
0_1_2 1
0_2_2 1
(4 row(s) affected)
*/
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-07-02 09:55:27
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(29))
insert [tb]
select '0,1,1,2,2,3,4,3,4' union all
select '0_1,0_2,0_3,1_2,1_3,1_3,1_2' union all
select '0_0_0,0_1_1,0_1_2,0_1_1,0_2_2'
--------------开始查询--------------------------
Select
COl=substring(a.Col,b.number,charindex(',',a.Col+',',b.number)-b.number),count(1) as 出现次数
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col)
where
substring(','+a.COl,b.number,1)=','
group by
substring(a.Col,b.number,charindex(',',a.Col+',',b.number)-b.number)
----------------结果----------------------------
/* COl 出现次数
----------------------------- -----------
0 1
0_0_0 1