22,209
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE(Field1 VARCHAR(10),Field2 VARCHAR(20))
INSERT @t VALUES('A','1001,1002,1003')
INSERT @t VALUES('B','1004,1005,1006')
INSERT @t VALUES('C','1003,1007,1008')
DECLARE @str VARCHAR(20)
SET @str='1001,1007,1009,2000'
;WITH SplitStr AS
(
SELECT
B.x.value('.','varchar(10)') AS Field
FROM (
SELECT
CONVERT(XML,'<v>'+REPLACE(@str,',','</v><v>')+'</v>') AS v
) AS A
CROSS APPLY A.v.nodes('//v') AS B(x)
)
SELECT
A.*
FROM @t AS A
JOIN SplitStr AS B
ON CHARINDEX(','+B.Field+',',','+A.Field2+',')>0
/*
Field1 Field2
---------- --------------------
A 1001,1002,1003
C 1003,1007,1008
(2 行受影响)
*/
FROM tb AS A
JOIN 分解的表 AS B
ON CHARINDEX(','+B.Field+',',','+A.Field2+',')>0