17,088
社区成员
请教一个查询数据中某个字段值发生变化的记录,表结构如下:
bh(编号) sl(数量) rq(日期)
a1 100 2022-1-1
a1 220 2022-1-2
a1 220 2022-1-3
a2 200 2022-1-1
a2 350 2022-1-2
a2 350 2022-1-3
..........................................
要求结果:根据编号,同一个编号的产品,数量发生变化后,就找到这条记录,同时计算变化前后的值,日期等字段,返回如下的数据集
bh sl_q(变化前数量) sl_h(变化后数量) sl_b(变化值) rq(日期)
a1 100 220 120 2022-1-2
a2 200 350 150 2022-1-2
...................................
create table t1(
id int,
name varchar(10),
datev date);
insert into t1(id,name,datev) values (1,'aa',to_date('2010-01-01','yyyy-mm-dd'));
insert into t1(id,name,datev) values(2,'ab',to_date('2010-01-01','yyyy-mm-dd'));
insert into t1(id,name,datev) values(3,'ac',to_date('2010-01-01','yyyy-mm-dd'));
insert into t1(id,name,datev) values(4,'ad',to_date('2010-01-01','yyyy-mm-dd'));
insert into t1(id,name,datev) values(5,'ae',to_date('2010-01-01','yyyy-mm-dd'));
insert into t1(id,name,datev) values (11,'aa',to_date('2010-01-02','yyyy-mm-dd'));
insert into t1(id,name,datev) values(22,'ab',to_date('2010-01-03','yyyy-mm-dd'));
insert into t1(id,name,datev) values(33,'ac',to_date('2010-01-02','yyyy-mm-dd'));
select name,id from t1;
select name,id,datev,lead(id) over(partition by name order by datev) id2,(lead(id) over(partition by name order by datev))-id cha
from t1
NAME ID DATEV ID2 CHA
aa 1 01-JAN-10 11 10
aa 11 02-JAN-10 - -
ab 2 01-JAN-10 22 20
ab 22 03-JAN-10 - -
ac 3 01-JAN-10 33 30
ac 33 02-JAN-10 - -
ad 4 01-JAN-10 - -
ae 5 01-JAN-10 - -
Download CSV