用于显示数据更改历史的 SQL 查询
我在 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:另一个不需要自连接但需要 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.
; 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.
相关文章