从具有历史行值的表生成更改历史

uklbhaso  于 2021-07-26  发布在  Java
关注(0)|答案(4)|浏览(270)

我有一个表,用于存储其他表中数据的历史值:

ObjectId | Value | UpdatedAt
1        |     A | 2020-07-15
1        |     B | 2020-07-16
1        |     C | 2020-07-17
2        |     A | 2020-07-15
2        |     B | 2020-07-16

现在我需要从这样的表中生成“change log”,它将显示什么是旧值、新值以及何时发生更新:

ObjectId | OldValue | NewValue | UpdatedAt
1        |     A    |        B | 2020-07-16
1        |     B    |        C | 2020-07-17
2        |     A    |        B | 2020-07-16

不幸的是,我不能更改现有表的结构,也不能将旧值放在那里,我需要一个查询来提取这个值。

axkjgtzd

axkjgtzd1#

你可以使用窗口功能 lead() 做分区 ObjectId . 这是演示。

select
  ObjectId,
  Value,
  NewValue,
  UpdatedAt
from
(
  select
    ObjectId,
    Value,
    lead(value) over (partition by ObjectId order by UpdatedAt) as NewValue,
    lead(UpdatedAt) over (partition by ObjectId order by UpdatedAt) as UpdatedAt
  from Table1
) subq
where NewValue is not null
order by ObjectId

输出:

| ObjectId  Value NewValue  UpdatedAt |

* -------------------------------------*

|  1        1       2      2020-07-16 |
|  1        2       3      2020-07-17 |
|  2        1       2      2020-07-16 |
6ioyuze2

6ioyuze22#

您可以使用窗口函数来实现该结果。

DECLARE @historyTable table(ObjectId int, Value int, UpdatedAt date)

insert into @historyTable values
(1        ,     1 ,'2020-07-15'),
(1        ,     2 ,'2020-07-16'),
(1        ,     3 ,'2020-07-17'),
(2        ,     1 ,'2020-07-15'),
(2        ,     2 ,'2020-07-16');

SELECT * from
(
SELECT objectid, value as oldvalue,
lead(value,1) over (partition by objectid order by updatedat) as newvalue,
lead(UpdatedAt,1) over (partition by objectid order by updatedat) as updatedat
FROM @historyTable
) as t
where t.updatedat is not null
+----------+----------+----------+------------+
| objectid | oldvalue | newvalue | updatedat  |
+----------+----------+----------+------------+
|        1 |        1 |        2 | 2020-07-16 |
|        1 |        2 |        3 | 2020-07-17 |
|        2 |        1 |        2 | 2020-07-16 |
+----------+----------+----------+------------+
jv4diomz

jv4diomz3#

with cte as (
     select row_number() over (partition by objectid order by UpdatedAt ) rnk,[ObjectId], [Value], [UpdatedAt]
     from T
)
select t1.ObjectId,t2.Value as OldValue ,t1.Value as NewValue ,t1.UpdatedAt
from (
    select * from cte 
    where rnk <> 1
) t1
left join cte t2 on t1.objectid =t2.objectid and t1.rnk -1 = t2.rnk

表脚本:

CREATE TABLE T
    ([ObjectId] int, [Value] int, [UpdatedAt] datetime)
;

INSERT INTO T
    ([ObjectId], [Value], [UpdatedAt])
VALUES
    (1, 1, '2020-07-15 00:00:00'),
    (1, 2, '2020-07-16 00:00:00'),
    (1, 3, '2020-07-17 00:00:00'),
    (2, 1, '2020-07-15 00:00:00'),
    (2, 2, '2020-07-16 00:00:00')
;

演示链接

4zcjmb1e

4zcjmb1e4#

with changeLogTemp as
    (
    select ROW_NUMBER() over (order by ObjectId,UpdatedAt ) row_number ,* from changeLog
    )

    select 
    l.ObjectId,
    l.Value OldValue,
    r.Value NewValue,
    r.UpdatedAt 

    from changeLogTemp l 
    left join changeLogTemp r on l.ObjectId =r.ObjectId and l.row_number =r.row_number-1
    where r.UpdatedAt is not null

你也可以使用 Widowfunction (Lead()) 为了解决这个问题
但所提出的解决方案非常简单,同时对类似的操作也非常有用( computational and comparative )在行和两列或多列之间

相关问题