内容
1,2,4
3,4,5,6,2
3,4,5
21,12,33
欲查询内容
2,4,3我的想法是,只要包含欲查询内容中的数字的内容要查出来。比如:欲查询内容中有2 4 3 ,那么应该查询来的数据为
1,2,4
3,4,5,6,2
3,4,5这样的SQL语句如何写?
1,2,4
3,4,5,6,2
3,4,5
21,12,33
欲查询内容
2,4,3我的想法是,只要包含欲查询内容中的数字的内容要查出来。比如:欲查询内容中有2 4 3 ,那么应该查询来的数据为
1,2,4
3,4,5,6,2
3,4,5这样的SQL语句如何写?
where co1 in(2,4,3) or co2 in (2,4,3) or co3 (2,4,3) or co4 (2,4,3)
where co1 in(2,4,3) or co2 in (2,4,3) or co3 in (2,4,3) or co4 in(2,4,3)
FROM
WHERE num LIKE'%2%' or num LIKE'%3%' or num LIKE'%4%'
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (col varchar(20))
INSERT INTO #T
SELECT '1,2,4' UNION ALL
SELECT '3,4,5,6,2' UNION ALL
SELECT '3,4,5' UNION ALL
SELECT '21,12,33'--SQL查询如下:DECLARE @str varchar(20);SET @str='2,4,3';SELECT DISTINCT col
FROM #T AS a
JOIN
(
SELECT
b.col2
FROM
(
SELECT col1=CONVERT(xml,'<root><v>'
+REPLACE(@str,',','</v><v>')+'</v></root>')
) AS a
CROSS APPLY
(
SELECT col2=v.value('.','nvarchar(100)')
FROM a.col1.nodes('/root/v') AS C(v)
) AS b
) AS b
ON CHARINDEX(','+b.col2+',',','+a.col+',')>0/*
col
--------------------
1,2,4
3,4,5
3,4,5,6,2(3 行受影响)
*/
INSERT INTO @t
SELECT '1,2,4' UNION ALL
SELECT '3,4,5,6,2' UNION ALL
SELECT '3,4,5' UNION ALL
SELECT '21,12,33'SELECT DISTINCT a.* FROM @t a
INNER JOIN
(
SELECT id=2
UNION
SELECT 4
UNION
SELECT 3
) b
ON CHARINDEX(',' + RTRIM(id) +',',','+v+',')>0
select * from 表
where ','+字段+',' like '%,2,%'
or ','+字段+',' like '%,4,%'
or ','+字段+',' like '%,3,%'