我在SQL Server数据库中有一个表,它每天存储历史数据。结构如下所示:
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 上发生了变化
我的要求是,我必须只显示那些与以前的值相比已更改的行。
在这种情况下,由于TypeID
1和2已经改变,它应该显示当前和最接近的先前值。对于TypeID
3,因为它没有改变,它只会显示最新的值。结果集如下所示:
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解决这个问题吗?
无灵感版本在有序集上使用自连接来检查同一类型ID按时间顺序排列的前一行的值。如果没有前一行或值不同,则输出该行。
; 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 小提琴。
更新:另一个不需要自加入但需要分组依据的变体。每个相同类型id,value1和value2的时间线都被赋予唯一的group_number稍后用于提取组的max(上传日期)。
; 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小提琴。