查询字符串中某个字符串出现的次数大于2的记录
ID sRemark
1 YI1203270018,YI1203290005,YI1203290006,YI1203290006,YC1203270049,
2 YI1111030008,YC1111030013,YC1111030012,YC1111210212,YC1111210212,3 YI1112270006,YC1112270043,YI1201050013,YC1201050029,YI1201110025,
4 YC1202170017,YI1202230013,YC1202230302,YC1202280100,YC1202280107,
5 YI1111030001,YC1111030001,YC1111030003,YC1111030003,YI1111230006,
上面的数据我想通过SQL来查询，查询出的结果是：
ID sRemark
1 YI1203270018,YI1203290005,YI1203290006,YI1203290006,YC1203270049,
2 YI1111030008,YC1111030013,YC1111030012,YC1111210212,YC1111210212,
5 YI1111030001,YC1111030001,YC1111030003,YC1111030003,YI1111230006,
就是查出sRemark中有两个或两个以上的相同单号的信息
不好意思我分不够了，只有11分了
[解决办法]
if OBJECT_ID('t_test','U') is not null drop table t_testgocreate table t_test( ID int, sRemark varchar(2000))goinsert into t_test(ID,sRemark)select 1,'YI1203270018,YI1203290005,YI1203290006,YI1203290006,YC1203270049,' union allselect 2,'YI1111030008,YC1111030013,YC1111030012,YC1111210212,YC1111210212,' union allselect 3,'YI1112270006,YC1112270043,YI1201050013,YC1201050029,YI1201110025,' union allselect 4,'YC1202170017,YI1202230013,YC1202230302,YC1202280100,YC1202280107,' union allselect 5,'YI1111030001,YC1111030001,YC1111030003,YC1111030003,YI1111230006,' gowith cte as( --一行转多行 select ID, convert(varchar(200),left(sRemark,CHARINDEX(',',sRemark)-1)) as col2, convert(varchar(200),RIGHT(sRemark,LEN(sRemark)-CHARINDEX(',',sRemark))) as temp from t_test union all select ID, convert(varchar(200),left(temp,CHARINDEX(',',temp)-1)) as col2, convert(varchar(200),RIGHT(temp,LEN(temp)-CHARINDEX(',',temp))) as temp from cte where len(temp)>4),cte2 as( --查询出出现重复的数据ID select ID, col2 from cte a group by a.ID,a.col2 having COUNT(col2)>1) --关联查询select b.* from cte2 aleft join t_test b on a.ID=b.IDorder by b.ID--ID sRemark--1 YI1203270018,YI1203290005,YI1203290006,YI1203290006,YC1203270049,--2 YI1111030008,YC1111030013,YC1111030012,YC1111210212,YC1111210212,--5 YI1111030001,YC1111030001,YC1111030003,YC1111030003,YI1111230006,