用于显示数据更改历史记录的 SQL 查询 [英] SQL query to show change history of data

问题描述

我在 SQL Server 数据库中有一个表，它每天存储历史数据.结构如下图:

I have a table in a SQL Server database which stores historical data on daily basis. The structure is shown below:

UploadDate    TypeID    Value1   Value2
-------------------------------------------
2012-01-08    1         NEG      1998-02-05
2012-01-08    2         NEG      1999-02-09
2012-01-08    3         STABLE   1997-02-06
2012-02-08    1         NEG      1998-02-05
2012-02-08    2         NEG      1999-02-09
2012-03-08    1         POS      2012-03-08
2012-03-08    2         STABLE   2012-01-08

正如您在上面看到的 TypeID 1 &2、Value1 和 Value2 已于 2012-03-08

As you can see above for the TypeID 1 & 2, Value1 and Value2 has changed on 2012-03-08

我的要求是我必须只显示那些与以前的值不同的行.

My requirement is such that I have to show only those rows which have changed from previous values.

在这种情况下，由于 TypeID 1 &2 已更改而不是应显示当前和最接近的先前值.而对于 TypeID 3，因为它没有改变，它只会显示最新的值.结果集如下所示:

In this case since TypeID 1 & 2 have changed than it should show the current and most nearest previous value. And for TypeID 3 since it has not changed, it will will only show the most current values. The result set would look something like below:

UploadDate    TypeID    Value1   Value2
-------------------------------------------
2012-01-08    3         STABLE   1997-02-06
2012-02-08    1         NEG      1998-02-05
2012-02-08    2         NEG      1999-02-09
2012-03-08    1         POS      2012-03-08
2012-03-08    2         STABLE   2012-01-08

知道如何使用 SQL 解决这个问题吗?

Any idea how I can tackle this using SQL?

推荐答案

Uninspired 版本在有序集合上使用自联接来检查按时间顺序排列的相同 typeid 的前一行的值.如果没有前一行或值不同，则输出该行.

Uninspired version uses self-join on ordered set to check the value of chronologically previous row of the same typeid. If there is no previous row or values are different the row is output.

; with numbered as (
  select *,
         row_number() over (order by typeid, uploaddate) rn
    from table1
)
select n1.*
  from numbered n1
  left join numbered n2
    on n1.TypeID = n2.TypeID
   and n1.rn + 1 = n2.rn
 where (n2.rn is null 
    or n1.value1 <> n2.value1
    or n1.value2 <> n2.value2)
 order by typeid, uploaddate

这是带有示例的 Sql Fiddle.

UPDATE:另一种不需要自加入但需要分组依据的变体.相同 typeid、value1 和 value2 的每个时间轴都被赋予唯一的 group_number，稍后用于为该组提取 max(uploaddate).

UPDATE: another variant which does not require self-join but does require group by. Each timeline of same typeid, value1 and value2 are given unique group_number which is used later to extract max(uploaddate) for the group.

; with numbered as (
  select *,
         row_number() over (order by typeid, uploaddate)
       - row_number() over (partition by typeid, value1, value2 
                            order by uploaddate) group_number
    from table1
)
select max(uploaddate) uploaddate, typeid, value1, value2
  from numbered
group by typeid, value1, value2, group_number
order by typeid, uploaddate

另一个 Sql Fiddle.

这篇关于用于显示数据更改历史记录的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！