Question

我正在尝试将两个具有相同模式的数据库合并在一起，这是其中的一部分。

我已经改变了主题以使其更易于理解——我无法改变这个模式，这正是我正在处理的问题。

我在源数据库和目标数据库中都有一个表，其中包含以下列：

Car
CarType1
CarType2
CarType3
CarType4

我正试图编写一个查询，告诉我目标数据库中的哪些行在两个数据库之间具有相同的Car，但具有不同的CarTypes。我所需要的只是对不同的行进行计数。

My query written in english would be: Bring me back a count of rows where the Car is the same and the CarTypes between the two systems do not match. It doesn t matter if the CarType is in a different CarType field between the two, just whether all of the values are contained in one of the 4 fields or not.

因此，如果在我的源数据库中这一行：

Car: Mustang
CarType1: Fast
CarType2: Convertible
CarType3: null
CarType4: null

在我的目标数据库中，我有这样一行：

Car: Mustang
CarType1: Fast
CarType2: Convertible
CarType3: Sports
CarType4: null

这将被视为不匹配，因为这是一辆野马，而且CarType字段的总和不同。值的顺序与此无关。

我将如何编写此查询？我抓不住它。

Answer 1

;WITH SourceT AS (
SELECT  Toyota  AS Car,  A  AS CarType1,  B  AS CarType2,  C  CarType3,  D  CarType4 UNION ALL
SELECT  BMW  AS Car,  A  AS CarType1,  B  AS CarType2,  C  CarType3,  D  CarType4 UNION ALL
SELECT  Mustang  AS Car,  Fast  AS CarType1,  Convertible  AS CarType2,  Sports  CarType3, NULL CarType4 
),
TargetT AS (
SELECT  Toyota  AS Car,  D  AS CarType1,  C  AS CarType2,  B  CarType3,  A  CarType4 UNION ALL
SELECT  BMW  AS Car,  D  AS CarType1,  C  AS CarType2,  B  CarType3,  A  CarType4  UNION ALL
SELECT  Mustang  AS Car,  Fast  AS CarType1,  Convertible  AS CarType2, NULL CarType3, NULL CarType4 )

SELECT *
FROM SourceT s
WHERE NOT EXISTS
(
SELECT * 
FROM TargetT t 
WHERE s.Car = t.Car AND 0 =
(SELECT COUNT(*) FROM 
    ( (
       (SELECT s.CarType1 AS t UNION ALL 
        SELECT s.CarType2 AS t UNION ALL 
        SELECT s.CarType3 AS t UNION ALL 
        SELECT s.CarType4 AS t )
    EXCEPT                
       (SELECT t.CarType1 AS t UNION ALL 
        SELECT t.CarType2 AS t UNION ALL 
        SELECT t.CarType3 AS t UNION ALL 
        SELECT t.CarType4 AS t )
        ) 
    UNION ALL
    (
       (SELECT t.CarType1 AS t UNION ALL 
        SELECT t.CarType2 AS t UNION ALL 
        SELECT t.CarType3 AS t UNION ALL 
        SELECT t.CarType4 AS t )
    EXCEPT                
       (SELECT s.CarType1 AS t UNION ALL 
        SELECT s.CarType2 AS t UNION ALL 
        SELECT s.CarType3 AS t UNION ALL 
        SELECT s.CarType4 AS t )
        )     
        ) T
    )
)

或者稍短的版本

SELECT *
FROM SourceT s
WHERE NOT EXISTS
(
SELECT * 
FROM TargetT t 
WHERE s.Car = t.Car AND 
(SELECT t FROM (SELECT s.CarType1 AS t UNION ALL 
                SELECT s.CarType2 AS t UNION ALL 
                SELECT s.CarType3 AS t UNION ALL 
                SELECT s.CarType4 AS t ) D ORDER BY t FOR XML PATH(  ))=
(SELECT t FROM (SELECT t.CarType1 AS t UNION ALL 
                SELECT t.CarType2 AS t UNION ALL 
                SELECT t.CarType3 AS t UNION ALL 
                SELECT t.CarType4 AS t ) D ORDER BY t FOR XML PATH(  ))
    )

Answer 2

试试这个，让我知道它是否有效：

SELECT * FROM db1.dbo.Cars
EXCEPT
SELECT c1.* FROM db1.dbo.Cars as c1
INNER JOIN db2.dbo.Cars as c2
ON    c1.Car = c2.Car
      AND
      --Check carType1
      (c1.CarType1 = c2.CarType1 OR 
      c1.CarType1 = c2.CarType2 OR 
      c1.CarType1 = c2.CarType3 OR 
      c1.CarType1 = c2.CarType4) 
      AND
      --Check carType2
      (c1.CarType2 = c2.CarType1 OR 
      c1.CarType2 = c2.CarType2 OR 
      c1.CarType2 = c2.CarType3 OR 
      c1.CarType2 = c2.CarType4)
      AND
      --Check carType3
      (c1.CarType3 = c2.CarType1 OR 
      c1.CarType3 = c2.CarType2 OR 
      c1.CarType3 = c2.CarType3 OR 
      c1.CarType3 = c2.CarType4)
      AND
      --Check carType4
      (c1.CarType4 = c2.CarType1 OR 
      c1.CarType4 = c2.CarType2 OR 
      c1.CarType4 = c2.CarType3 OR 
      c1.CarType4 = c2.CarType4)

注意：

如果列为null，则必须将ISNULL添加到其中以包含或排除。

要排除：

ISNULL(c1.CarType4, -1) = ISNULL(c2.CarType4, -2)

包括：

ISNULL(c1.CarType4, -1) = ISNULL(c2.CarType4, -1)

Answer 3

SELECT c1.car
FROM target.cars c1
INNER JOIN source.cars c2
ON c2.car = c1.car
WHERE
COALESCE( c1.cartype1,   ) NOT IN 
(   , c2.cartype1, c2.cartype2, c2.cartype3, c2.cartype4 )
OR
COALESCE( c1.cartype2,   ) NOT IN 
(   , c2.cartype1, c2.cartype2, c2.cartype3, c2.cartype4 )
OR
COALESCE( c1.cartype3,   ) NOT IN 
(   , c2.cartype1, c2.cartype2, c2.cartype3, c2.cartype4 )
OR
COALESCE( c1.cartype4,   ) NOT IN 
(   , c2.cartype1, c2.cartype2, c2.cartype3, c2.cartype4 )
OR
LEN( COALESCE( c1.cartype1,   ) + COALESCE( c1.cartype2,   ) +
 COALESCE( c1.cartype3,   ) + COALESCE( c1.cartype4,   ) ) 
<>
LEN( COALESCE( c2.cartype1,   ) + COALESCE( c2.cartype2,   ) + COALESCE( c2.cartype3,   ) + 
COALESCE( c2.cartype4,   ) )
;

友情链接