表A内有字段ID和QSDW(签收单位),数据如下
ID QSDW
1 1,2,3,4表B内有字段ID和QSID(签收单位ID),数据如下
ID QSID
1 1
1 2
现在要求查询出未签收的单位ID,即查询出 3和4 SQL数据库not in数组
ID QSDW
1 1,2,3,4表B内有字段ID和QSID(签收单位ID),数据如下
ID QSID
1 1
1 2
现在要求查询出未签收的单位ID,即查询出 3和4 SQL数据库not in数组
(
select 1 id,'1,2,3,4'QSDW
)aselect * into #b from
(
select 1 id,1 QSID
union all select 1,2
)a
select a.id,b.Col2
from (
select id,convert(xml,'<row><b>'+rtrim(replace(QSDW,',','</b><b>'))+'</b></row>') as Col2 from #a)a
outer apply (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/row/b')C(v))b
where b.col2 not in (select QSID from #b)/*
id Col2
-----------
1 3
1 4(2 行受影响)*/