用于显示数据更改历史的 SQL 查询
问题描述
我在 SQL Server 数据库中有一个表,它每天存储历史数据.结构如下图:
I have a table in a SQL Server database which stores historical data on daily basis. The structure is shown below:
正如您在上面看到的 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:
知道如何使用 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.
这里是 Sql Fiddle 示例.
UPDATE:另一个不需要自连接但需要 group by 的变体.每个具有相同 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.
另一个 Sql Fiddle.
这篇关于用于显示数据更改历史的 SQL 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!