DECLARE @a NVARCHAR(100)= '01,02,04,05,07';
DECLARE @b NVARCHAR(100)= '01,03,04,05,06';
WITH a1
AS ( SELECT CONVERT(XML, '<root><v>' + REPLACE(@a, ',', '</v><v>')
+ '</v></root>') cxml
),
a AS ( SELECT v.value('.', 'varchar(100)') cv
FROM a1
CROSS APPLY cxml.nodes('/root/v') AS C ( v )
),
b1
AS ( SELECT CONVERT(XML, '<root><v>' + REPLACE(@b, ',', '</v><v>')
+ '</v></root>') cxml
),
b AS ( SELECT v.value('.', 'varchar(100)') cv
FROM b1
CROSS APPLY cxml.nodes('/root/v') AS C ( v )
),
x1
AS ( SELECT 1 AS z ,
a.cv
FROM a
JOIN b ON a.cv = b.cv
),
x0
AS ( SELECT z ,
( SELECT cv + ','
FROM x1
WHERE z = t1.z
ORDER BY z
FOR
XML PATH('')
) AS l
FROM x1 t1
GROUP BY z
)
SELECT LEFT(l, LEN(l) - 1) AS vs
FROM x0;
vs
----------------------------------------------------------------
01,04,05
(1 行受影响)