SQL Server：FULL JOIN选择，管理表A和B之间的重复项

问题描述：

我在查询中有一个复杂的（对我来说）两个表的SQL Server连接。我有两个表执行相同的功能与类似的结构（但单独更新），并正在写一个查询来找出两者之间的差异，以便他们可以更好地保持相对同步。这些表格与以下内容类似：SQL Server：FULL JOIN选择，管理表A和B之间的重复项

编辑：请在底部添加SQL小提琴链接以增加清晰度。

Table A 
+-----+---------+----------------+------------+-----------+ 
| ID | Product | ValidationDate | ValidValue | ValidHour | 
+-----+---------+----------------+------------+-----------+ 
| 991 |  1 | 1/1/2015  |  100 | 8:00  | 
| 992 |  1 | 1/2/2015  |  100 | 8:00  | 
| 993 |  1 | 1/2/2015  |  100 | 8:00  | 
| 994 |  1 | 1/3/2015  |   88 | 9:00  | 
| 995 |  2 | 1/1/2015  |   98 | 9:00  | 
| 996 |  2 | 1/1/2015  |   75 | 10:00  | 
| 997 |  2 | 1/5/2015  |   80 | 10:00  | 
+-----+---------+----------------+------------+-----------+ 

Table B 
+----+---------+----------------+------------+-----------+ 
| ID | Product | ValidationDate | ValidValue | ValidHour | 
+----+---------+----------------+------------+-----------+ 
| 1 |  1 | 1/1/2015  |  100 | 8:00  | 
| 2 |  1 | 1/2/2015  |  100 | 8:00  | 
| 3 |  1 | 1/3/2015  |   88 | 9:00  | 
| 4 |  2 | 1/1/2015  |   98 | 8:00  | 
| 5 |  2 | 1/1/2015  |   75 | 10:00  | 
+----+---------+----------------+------------+-----------+

该查询的目的是通过Product，ValidationDate，ValidValue，和ValidHour返回两个表之间的差异。如果有两个之间的不匹配，我想显示两个表中的结果为特定Product，ValidationDate，ValidValue和ValidHour如下：

+---------+-----------+---------+---------+---------+--------+ 
| Product | ValidDate | A_Value | A_Grade | B_Value | B_Hour | 
+---------+-----------+---------+---------+---------+--------+ 
|  1 | 1/2/2015 |  100 | 8:00 |   |  | 
|  1 | 1/2/2015 |  100 | 8:00 |   |  | 
|  1 | 1/2/2015 |   |   |  100 | 8:00 | 
|  2 | 1/5/2015 |  80 | 10:00 |   |  | 
+---------+-----------+---------+---------+---------+--------+

正如你看到的，有表之间存在不均衡相同的条目A（ID 992,993）和表B（ID 2），所以输出表A条目以及单个表B条目。此外，表A还包含表B中未找到的条目，该条目也会显示。

我最初创建类似于下面的语句：

SELECT Product, ValidDate, a.Value, a.Grade, b.Value, b.Hour 
FROM Table_A a 
FULL OUTER JOIN Table_B b 
    ON a.Product = b.Product AND 
    a.ValidDate = b.ValidDate AND 
    a.ValidValue = b.ValidValue AND 
    a.ValidHour = b.ValidHour AND 
WHERE (a.Product IS NULL AND 
    a.ValidDate IS NULL AND 
    a.ValidValue IS NULL AND 
    a.ValidHour IS NULL) 
    OR 
    (b.Product IS NULL AND 
    b.ValidDate IS NULL AND 
    b.ValidValue IS NULL AND 
    b.ValidHour IS NULL)

但是，它并没有真正的工作，因为即使它返回在高于所需输出的第三列正确的结果（其中单个行存在于一个表中，但在其他表中不存在），它忽略了表之间存在不均匀重复条目的情况。

我该如何在查询中完成所需的工作？

编辑：我已经在SQL小提琴创建了一个更清晰的示例我的情况和欲望：http://sqlfiddle.com/#!3/f8a92/4我已经评论了解释。

答

我使用了2个表格表达式，并且除了聚合列以外所有列上的表达式都加入了它们。在聚合列存在差异的情况下，查询返回该行。希望这可以帮助。

With A as 
(
    select Date, Product, ValidValue, SomeValue2, 
     count(*) as NumOfOccurences 
    from dbo.TableA 
    group by Date, Product, ValidValue, SomeValue2 
) 
, 
b as 
(
    select Date, Product, ValidValue, SomeValue2, 
      count(*) as NumOfOccurences 
    from dbo.TableB 
    group by Date, Product, ValidValue, SomeValue2 

) 

select * 
from a join b on A.date = b.date and 
         A.product = b.product and 
         A.ValidValue = b.ValidValue and 
         A.SomeValue2 = b.SomeValue2 and 
         A.NumOfOccurences <> b.NumOfOccurences;

答

你所要求的是，虽然可能不是不可能的，但是会以代码方式参与其中。所有DBMS按照集合运行，并且集合的一个最重要的特征是不允许重复。如果允许具有相同非键值的多个条目，则不再使用集合。

到目前为止，最简单的解决方案是截断TableB并复制表A中的所有内容。

truncate TableB; 
insert into TableB(...) 
    select ... from TableA;

我假设两个表的ID字段都是自动生成的。我还假设有一些关于防止触发器保持同步的表格。这将是最佳的长期解决方案。

如果表格实际上应该具有相同的内容，那么最佳解决方案将是完全删除辅助表格。 :) – 2015-02-12 17:51:58

将主表内容传播到一个或多个副本有很多原因 - 通常位于远程位置的不同服务器上。我们不知道详情，我们可以假设有一个很好的理由。实际上，在考虑它的时候，最好的解决方案可能是一个物化视图，它们的更新时间表重新一致。可能存在一些缺点，例如同步期间大量的网络流量，但它可能是可能的 – TommCatt 2015-02-12 18:38:57

答

好的。感谢sqlfiddle。这使得这件事很容易处理。为了完整起见，我将在此粘贴您的小提琴。

CREATE TABLE [dbo].[TableA](
    [ID] [int] NOT NULL, 
    [Product] [int] NOT NULL, 
    [Date] [datetime] NOT NULL, 
    [ValidValue] [int] NOT NULL, 
    [SomeValue2] [int] NOT NULL 
) ON [PRIMARY] 

CREATE TABLE [dbo].[TableB](
    [ID] [bigint] NOT NULL, 
    [Product] [int] NOT NULL, 
    [Date] [datetime] NOT NULL, 
    [ValidValue] [smallint] NOT NULL, 
    [SomeValue2] [smallint] NOT NULL 
) ON [PRIMARY] 

INSERT INTO TableA SELECT 991,1,'1/1/15',75, 7; 
INSERT INTO TableA SELECT 992,1,'1/2/15',100,8; --these two are identical values in TableA, 
INSERT INTO TableA SELECT 993,1,'1/2/15',100,8; --Possibly erroneous duplication 

INSERT INTO TableB SELECT 1, 1,'1/2/15',100,8; --In TableB, there is only a single matching value 
INSERT INTO TableB SELECT 1, 1,'1/3/15',90, 6;

因此，现在为了能够识别重复您还需要使用ROW_NUMBER。我们可以通过对每个基表使用一个cte来实现这一点。然后稍微修改的连接，它会nonmatched对于任何一个表A OR表B

with MyTableA as 
(
    select * 
     , ROW_NUMBER() over(partition by Product, Date order by ID) as RowNum 
    from TableA a 
) 
, MyTableB as 
(
    select * 
    , ROW_NUMBER() over(partition by Product, Date order by ID) as RowNum 
    from TableB 
) 

SELECT CASE WHEN a.Date IS NULL 
      THEN b.Date 
      ELSE a.Date 
      END Date, 
     CASE WHEN a.Product IS NULL 
      THEN b.Product 
      ELSE a.Product 
      END Product, 
     a.ValidValue A_ValidValue, 
     a.SomeValue2 A_SomeValue2, 
     b.ValidValue B_ValidValue, 
     b.SomeValue2 B_SomeValue2 
FROM MyTableA a 
FULL OUTER JOIN MyTableB b 
    ON a.Product = b.Product AND 
    a.Date = b.Date AND 
    a.ValidValue = b.ValidValue AND 
    a.SomeValue2 = b.SomeValue2 
    AND a.RowNum = b.RowNum 
WHERE (a.Product IS NULL OR 
    a.Date IS NULL OR 
    a.ValidValue IS NULL OR 
    a.SomeValue2 IS NULL) 
    OR 
    (b.Product IS NULL OR 
    b.Date IS NULL OR 
    b.ValidValue IS NULL OR 
    b.SomeValue2 IS NULL)

我删除了我以前的答案和sqlfiddle联系是在评论中重复。这是一个链接到原始的sqlfiddle。 http://sqlfiddle.com/#!3/f8a92/11

SQL Server：FULL JOIN选择，管理表A和B之间的重复项

相关推荐