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

我有很多大表,每个表都有> 300列.我正在使用的应用程序通过在辅助表中创建当前行的副本来创建已更改行的“存档”.

考虑一个简单的例子：

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
;

（编辑：安卓应用网）

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