sql-server – 查询详细说明大量数据的行之间的差异

考虑一个简单的例子：

CREATE TABLE dbo.bigtable
(
  UpdateDate datetime,PK varchar(12) PRIMARY KEY,col1 varchar(100),col2 int,col3 varchar(20),.
  .
  .
  colN datetime
);

档案表：

CREATE TABLE dbo.bigtable_archive
(
  UpdateDate datetime,PK varchar(12) NOT NULL,.
  .
  .
  colN datetime
);

在dbo.bigtable上执行任何更新之前,将在dbo.bigtable_archive中创建该行的副本,然后使用当前日期更新dbo.bigtable.UpdateDate.

因此将两张桌子联合起来在按UpdateDate排序时,按PK分组会创建更改的时间轴.

我希望创建一个报告,详细说明由UpdateDate排序的行之间的差异,按PK分组,格式如下：

PK,UpdateDate,ColumnName,Old Value,New Value

旧值和新值可以是转换为VARCHAR(MAX)的相关列(不涉及TEXT或BYTE列),因为我不需要对值本身进行任何后处理.

目前我无法想到一个理智的方法来为大量的列执行此操作,而无需以编程方式生成查询 – 我可能必须这样做.

开放给很多想法,所以我会在2天后为问题添加赏金.

解决方法

> UNION这两张桌子.
>对于组合集中的每个PK,从归档表中获取其先前的“化身”(下面的实现使用OUTER APPLY TOP(1)作为穷人的LAG).
>将每个数据列转换为varchar(max)并成对地取消它们,即当前值和之前的值(CROSS APPLY(VALUES …)适用于此操作).
>最后,根据每对中的值是否彼此不同来过滤结果.

我看到的上面的Transact-SQL：

WITH
  Combined AS
  (
    SELECT * FROM dbo.bigtable
    UNION ALL
    SELECT * FROM dbo.bigtable_archive
  ) AS derived,OldAndNew AS
  (
    SELECT
      this.*,OldCol1 = last.Col1,OldCol2 = last.Col2,...
    FROM
      Combined AS this
      OUTER APPLY
      (
        SELECT TOP (1)
          *
        FROM
          dbo.bigtable_archive
        WHERE
          PK = this.PK
          AND UpdateDate < this.UpdateDate
        ORDER BY
          UpdateDate DESC
      ) AS last
  )
SELECT
  t.PK,t.UpdateDate,x.ColumnName,x.OldValue,x.NewValue
FROM
  OldAndNew AS t
  CROSS APPLY
  (
    VALUES
    ('Col1',CAST(t.OldCol1 AS varchar(max),CAST(t.Col1 AS varchar(max))),('Col2',CAST(t.OldCol2 AS varchar(max),CAST(t.Col2 AS varchar(max))),...
  ) AS x (ColumnName,OldValue,NewValue)
WHERE
  NOT EXISTS (SELECT x.OldValue INTERSECT x.NewValue)
ORDER BY
  t.PK,x.ColumnName
;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!