时间戳

首页 > 解决方案 > 在sql中将每条记录回溯到其原始状态

sql - 在sql中将每条记录回溯到其原始状态

问题描述

我想回溯每条记录以找到它的所有状态。学生表仅包含更新的值，但学生历史记录包含更新列的旧值。

create table student (    
    id number(6) primary key,
    name varchar2(50),
    city varchar2(50),
    address varchar2(100),
    createdDateTime date,
    updatedDatetime date
);

insert into student values(1,'abc1','abc1','abc1','09-Jan-20','12-Jan-20');
insert into student values(2,'pqr','pqr','pqr','09-Jan-20',null);

学生桌——

ID      Name    City     Address    Create_time UpdatedTime
1       abc1    abc1     abc1       09-Jan-20   12-Jan-20
2       pqr     pqr      pqr        09-Jan-20   null

create table studentHistory (
    id number(6) ,
    name varchar2(50),
    city varchar2(50),
    address varchar2(100),
    DatetimeCreated date
);

insert into StudentHistory values(1,null,'abc',null,'10-Jan-20');
insert into StudentHistory values(1,'abc',null,null,'11-Jan-20');
insert into StudentHistory values(1,null,null,'abc','12-Jan-20');

学生历史表-

ID  Name    City        Address DatetimeCreated
1   null    abc          null     10-Jan-20
1   abc     null         null     11-Jan-20
1   null    null         abc      12-Jan-20

所需的输出将是单个记录的每个阶段

ID  Name    City         Address    DatetimeCreated     LastUpdated
1   abc     abc         abc        09-Jan-20            null
1   abc    abc1         abc        09-Jan-20          10-Jan-20
1   abc1    abc1        abc        09-Jan-20          11-Jan-20
1   abc1    abc1        abc1      09-Jan-20       12-Jan-20
2   pqr     pqr         pqr        09-Jan-20           null

标签： sqloracle11g

解决方案

您可以使用逻辑重构历史。对于给定的列，该值是NULL按此顺序遇到的第一个非值：

  1. 特定行中的值。
  2. NULL学生的前一行中的前一个非值
  3. 价值在student

这解决了大部分问题。然后，您需要引入最新数据。作为查询：

select sh.id,
       coalesce(sh.name,
                lag(sh.name ignore nulls) over (partition by sh.id order by sh.DatetimeCreated),
                s.name
               ) as name,
       coalesce(sh.city,
                lag(sh.city ignore nulls) over (partition by sh.id order by sh.DatetimeCreated),
                s.city
               ) as city,
       coalesce(sh.address,
                lag(sh.address ignore nulls) over (partition by sh.id order by sh.DatetimeCreated),
                s.address
               ) as address,
        s.createdDateTime,
        sh.createdDateTime as updatedDateTime
from studenthistory sh join
     student s
     on s.id = sh.id
union all
select s.id, s.name, s.city, s.address, s.createdDateTime, s.updatedDateTime
from student s;

推荐阅读