如何检查来自不同表的两列的内容是否相同？

Question

我的SQL数据库中有两个表。我想检查Specifier列是否具有完全相同的顺序的数据。

OK case，因为两个表在Specifier列中具有相同的数据顺序：

-- Table1:
RowID   Specifier
187     1         
188     1         
189     2         

-- Table2:
RowID   Specifier
181     1         
182     1         
183     2       

错误大小写，因为数据是不同的：

-- Table1:
RowID   Specifier
187     1         
188     2         
189     3         

-- Table2:
RowID   Specifier
181     1         
182     2         
183     2    

错误大小写，因为数据的顺序不同：

-- Table1:
RowID   Specifier
187     1         
188     1         
189     2         

-- Table2:
RowID   Specifier
181     1         
182     2         
183     1   

错误案例，因为不同数据量：

-- Table1:
RowID   Specifier
187     1         
188     1         
189     2         

-- Table2:
RowID   Specifier
181     1         
182     1         
183     2
184     1       

我编写了以下查询，该查询几乎正常工作，如果一个表的值为另一个表的值而另一个表的值不为另一个表的值，则该查询将正确地给出一个错误，但如果只有顺序不正确，则不会出现错误：

IF EXISTS
    (SELECT Specifier FROM Table1 EXCEPT SELECT Specifier FROM Table2
    UNION ALL
    SELECT Specifier FROM Table2 EXCEPT SELECT Specifier FROM Table1)
BEGIN
    THROW 99999, 'Mismatching Specifiers between the two tables', 1;
END;

Answer 1

您可以使用full join和row_number()。以下是例外情况：

select *
from (select t1.*, row_number() over (order by rowid) as seqnum
      from table1 t1
     ) t1 full join
     (select t2.*, row_number() over (order by rowid) as seqnum
      from table2 t2
     ) t2
     on t1.seqnum = t2.seqnum and t1.specifier = t2.specifier
where t1.seqnum is null or t2.seqnum is null;

如果您只是想要一个旗子：

select (case when count(*) > 1 then 1 else 0 end)
from (select t1.*, row_number() over (order by rowid) as seqnum
      from table1 t1
     ) t1 full join
     (select t2.*, row_number() over (order by rowid) as seqnum
      from table2 t2
     ) t2
     on t1.seqnum = t2.seqnum and t1.specifier = t2.specifier
where t1.seqnum is null or t2.seqnum is null;

如果您关心性能，那么在exists中使用第一个查询应该会更快。

Answer 2

看上去可能更容易

IF EXISTS (SELECT 1
           FROM (SELECT ROW_NUMBER() OVER (ORDER BY RowID) AS RN,
                        Specifier
                 FROM Table1) T1
                FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY RowID) AS RN,
                                        Specifier
                                 FROM Table2) T2 ON T1.RN = T2.RN
                                                AND T1.Specifier = T2.Specifier
           HAVING COUNT(CASE WHEN T1.RN IS NULL OR T2.RN IS NULL THEN 1 END) >= 1) ...