比较具有相同模式的两个表中的数据 [英] Compare the data in two tables with same schema

问题描述

我已经花了一段时间寻找一个特定的问题，但是我找不到这个特定的问题

I have been doing a bit of searching for a while now on a particular problem, but I can't quite find this particular question

在SQL中我要完成一个非常不寻常的任务:

I have a rather unusual task to achieve in SQL:

我有两个表，例如A和B，它们具有完全相同的列名，格式如下:

I have two tables, say A and B, which have exactly the same column names, of the following form:

id | column_1 | ... | column_n

两个表具有相同的行数和相同的ID，但是对于给定的ID，表A和B中的行有可能在其他一个或多个其他列中有所不同.

Both tables have the same number of rows, with the same id's, but for a given id there is a chance that the rows from tables A and B differ in one or more of the other columns.

我已经有一个查询，该查询返回表A中的所有行，而表B中的对应行不相同，但是我需要的是一个返回以下形式的查询:

I already have a query which returns all rows from table A for which the corresponding row in table B is not identical, but what I need is a query which returns something of the form:

id | differing_column
----------------------
1  | column_1
3  | column_6

表示ID为'1'的行在表A和B中具有不同的'column_1'值，而ID'3'的行在表A和B中具有不同的'column_6'值.

meaning that the row with id '1' has different 'column_1' values in tables A and B, and the row with id '3' has different 'column_6' values in tables A and B.

这完全可以实现吗?我想象它可能需要某种方式才能将列名作为值来获取，但是我可能是错的.非常感谢您的帮助/建议.

Is this at all achievable? I imagine it might require some sort of pivot in order to get the column names as values, but I might be wrong. Any help/suggestions much appreciated.

推荐答案

是的，您可以使用以下查询来做到这一点:

Yes you can do that with a query like this:

WITH Diffs (Id, Col) AS (
    SELECT
        a.Id,
        CASE
            WHEN a.Col1 <> b.Col1 THEN 'Col1'
            WHEN a.Col2 <> b.Col2 THEN 'Col2'
            -- ...and so on
            ELSE NULL
        END as Col
    FROM TableOne a
    JOIN TableTwo b ON a.Id=b.Id
)
SELECT Id, Col
WHERE Col IS NOT NULL

请注意，上述查询不会返回所有具有差异的列，而只会返回要查找的第一个列.

Note that the above query is not going to return all the columns with differences, but only the first one that it is going to find.

这篇关于比较具有相同模式的两个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！