比较两个表,并显示该结果作为一个单独的输出
我有两个表和这样的值,'比较两个表,并显示该结果作为一个单独的输出
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,'&','&'), ',', '</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
使用递归来避免使用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
我需要它没有xml路径 –
而且如果插入InputLocation值(2,'Loc3,Loc4'),那么我也需要输出 –
这意味着它应该检查第二表 –
你的脚本就可以了。
另一种方法是使用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
谢谢虽然,但我需要它没有xml路径 –
而且如果插入到InputLocation值(2,'Loc3,Loc4'),那么我也需要输出 –
这意味着它应该检查第二个表 –
什么是它不possible.Secondly你应该从技术上说清楚,既table.without关系之间的关系,为什么你不希望使用XML路径?如果是性能相关的,那么对不起你不能正确地覆盖您的要求。其他方式可以是递归CTE,但其性能最差。 – KumarHarsh
id是关系 –
性能现在不是我的首选 –