SQL Server利用XML找字符串相同部分

DECLARE?@a?NVARCHAR(100)=?'01,02,04,05,07';
DECLARE?@b?NVARCHAR(100)=?'01,03,04,05,06';
WITH????a1
??????????AS?(?SELECT???CONVERT(XML,?''?+?REPLACE(@a,?',',?'')
????????????????????????+?'')?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,?''?+?REPLACE(@b,?',',?'')
????????????????????????+?'')?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 行受影響)