比较两个表，并显示该结果作为一个单独的输出

Question

我有两个表和这样的值，'

CREATE TABLE Location (ID int ,Location Varchar(500)) 
    INSERT INTO Location values (1,'Loc3'),(2,'Loc4'),(3,'Loc5'),(4,'Loc7') 
    CREATE TABLE InputLocation (ID int ,Location Varchar(500)) 
    Insert into InputLocation values(1,'Loc1,Loc2,Loc3,Loc4,Loc5,Loc6') 

我需要通过与表InputLocation和需要从表Location各值进行匹配，以获得输出显示两者不带第2台相匹配的输出，即Loc1,Loc2,Loc6，我已经试过像这样的代码，它的工作，但我需要更简单的代码，任何帮助将不胜感激

我的代码：

SELECT STUFF((select ','+ Data.C1 
FROM 
(select 
    n.r.value('.', 'varchar(50)') AS C1 
from InputLocation as T 
cross apply (select cast('<r>'+replace(replace(Location,'&','&amp;'), ',', '</r><r>')+'</r>' as xml)) as S(XMLCol) 
cross apply S.XMLCol.nodes('r') as n(r)) DATA 
WHERE data.C1 NOT IN (SELECT Location 
       FROM Location) for xml path('')),1,1,'') As Output 

Answer 1

使用递归来避免使用XML慢阅读：

;with tmp(DataItem, Location) as (
select cast(LEFT(Location, CHARINDEX(',',Location+',')-1) as nvarchar(50)), 
    cast(STUFF(Location, 1, CHARINDEX(',',Location+','), '') as nvarchar(50)) 
from [InputLocation] 
union all 
select cast(LEFT(Location, CHARINDEX(',',Location+',')-1) as nvarchar(50)), 
    cast(STUFF(Location, 1, CHARINDEX(',',Location+','), '') as nvarchar(50)) 
from tmp 
where Location > '' 
) 
select STUFF((SELECT ',' + x.Location 
from (
select DataItem as Location from tmp 
except Select Location from [Location]) x 
FOR XML path('')), 1, 1, '') AS OUTPUT 

Answer 2

你的脚本就可以了。

另一种方法是使用SPLIT字符串，如此处所述。 http://www.sqlservercentral.com/articles/Tally+Table/72993/

使用[DBO]。[DelimitedSplit8K]

假设我的逗号分隔字符串不会比500更长，然后在我的自定义UDF我让500 VARCHAR，而不是为varchar（8000），以提高性能。

SELECT STUFF((
      SELECT ',' + Data.item 
      FROM (
       SELECT il.ID 
        ,fn.item 
       FROM @InputLocation IL 
       CROSS APPLY (
        SELECT * 
        FROM dbo.DelimitedSplit2K(il.Location, ',') 
        ) fn 
       WHERE NOT EXISTS (
         SELECT * 
         FROM @Location L 
         WHERE l.Location = fn.Item 
         ) 
       ) data 
      FOR XML path('') 
      ), 1, 1, '') AS 
OUTPUT