如何使sql视图显示数据差异

Question

最近我正在为我的数据库准备编辑历史记录。我制作了一个表，用于保存更新前后的记录。我在历史表中添加了两条记录配对的列(更新前和更新后的那条)。所以我有完整的历史记录表。

我现在想做的是创建复杂的(在我看来)视图。我想要显示该表中所有记录之间的所有差异。让我解释一下。

我在该表中有20列，但让我们取4列来说明我的意思: Title/Author/Date/PairIndex。这些是列名。

和数据：

Book1/Author1/30-03-1990/1
Book1/Author1/30-04-1990/1
Book2/Author2/30-03-2004/2
Book3/Author3/30-03-2004/2

这里我有两个更新。第一次更新只是更改了日期。第二次更新更改了书名和作者。我想在我的视图中显示如下内容：

1-- 30-03-1990 -- 30-04-1990
2-- Book2 -- Book3
2-- Author2 -- Author3

因此，每个差异都有自己的行，其中包含不同的数据。这是为具有相同PairIndex的每一对记录创建的。

下面是我的不起作用的代码：

Create table Boom ( Book   VARCHAR(10),
                             author VARCHAR(10),
                             date     DATE,
                             id     INT )

INSERT INTO Boom
VALUES ( 'Book1', 'Author1', '19900330', 1 ),
       ( 'Book1', 'Author1', '19900430', 1 ),
       ( 'Book2', 'Author2', '20040330', 2 ),
       ( 'Book3', 'Author3', '20040330', 2 );

WITH cte
AS (
      SELECT Boom.*, ROW_NUMBER() over (partition by ID order by author) as RN
      FROM Boom
   ) 

select b1.id, b2.id, b1.date, b2.date
  from cte b1 
  join cte b2 
    on b1.id = b2.id 
   and b1.rn < b2.rn 
   and b1.date <> b2.date

select b1.id, b2.id, b1.author, b2.author
  from cte b1 
  join cte b2 
    on b1.id = b2.id 
   and b1.rn < b2.rn 
   and b1.author<> b2.author

Answer 1

尝试如下所示：

DECLARE @SomeData AS TABLE ( Book   VARCHAR(10),
                             Author VARCHAR(10),
                             Dt     DATE,
                             ID     INT )

INSERT INTO @SomeData
VALUES ( 'Book1', 'Author1', '19900330', 1 ),
       ( 'Book1', 'Author1', '19900430', 1 ),
       ( 'Book2', 'Author2', '20040330', 2 ),
       ( 'Book3', 'Author3', '20040330', 2 );
WITH cte
AS (
    SELECT *, ROW_NUMBER() over (partition by ID order by Book, Author, Dt) as RN
    FROM @SomeData
   )

SELECT a.ID
      ,a.Book
      ,a.Author
      ,a.Dt
      ,b.Book
      ,b.Author
      ,b.Dt
FROM cte AS a LEFT JOIN cte AS b ON a.id = b.id
                                AND a.RN < b.RN
WHERE a.RN = 1

Answer 2

需要row_number，然后再找出差异

WITH cte
AS (
      SELECT table.*, ROW_NUMBER() over (partition by ID order by author) as RN
      FROM Table
   ) 

select b1.id, b2.id, b1.date, b2.date
  from cte b1 
  join cte b2 
    on b1.id = b2.id 
   and b1.rn < b2.rn 
   and b1.date <> b2.date;

WITH cte
    AS (
          SELECT table.*, ROW_NUMBER() over (partition by ID order by author) as RN
          FROM Table
       ) 

select b1.id, b2.id, b1.author, b2.author
  from cte b1 
  join cte b2 
    on b1.id = b2.id 
   and b1.rn < b2.rn 
   and b1.author<> b2.author;