sql - 显示表中列更改的查询

Question

我有一张表，其中包含我的产品表的日志，如下所示:

process_time             product_id  product_type_id
04.07.2009 14:08:43      5           4
05.07.2009 15:08:43      5           4
06.07.2009 16:08:43      5           6
07.07.2009 16:08:43      5           6
08.07.2009 17:08:43      5           4
08.07.2009 18:08:43      5           4

我想写一个查询来显示 product_type_id 的变化。对于上面的例子，我的查询结果应该是这样的:

process_time             product_id  product_type_id
04.07.2009 14:08:43      5           4
06.07.2009 16:08:43      5           6
08.07.2009 17:08:43      5           4

我该如何编写此查询？

Answer 1

像这样:

select * from
(select process_time, product_id, product_type_id
,lag(product_type_id) over (partition by product_id order by process_time) as prevrow
,lead(product_type_id) over (partition by product_id order by process_time) as nextrow
from products )
where nextrow <> product_type_id or nextrow is null;

对于所有喜欢看看它是如何工作的:

create table products (process_time  timestamp, product_id number, product_type_id number);

insert into products values (to_date('2009-07-04 14:08:43','YYYY-MM-DD hh24:mi:ss'),5,4);
insert into products values (to_date('2009-07-05 15:08:43','YYYY-MM-DD hh24:mi:ss'),5,4);
insert into products values (to_date('2009-07-06 16:08:43','YYYY-MM-DD hh24:mi:ss'),5,6);
insert into products values (to_date('2009-07-07 16:08:43','YYYY-MM-DD hh24:mi:ss'),5,6);
insert into products values (to_date('2009-07-08 17:08:43','YYYY-MM-DD hh24:mi:ss'),5,4);
insert into products values (to_date('2009-07-08 18:08:43','YYYY-MM-DD hh24:mi:ss'),5,4);

commit;

select process_time, product_id, product_type_id
,lag(product_type_id) over (partition by product_id order by process_time) as prevrow
,lead(product_type_id) over (partition by product_id order by process_time) as nextrow
from products 
order by process_time;

select * from
(select process_time, product_id, product_type_id
,lag(product_type_id) over (partition by product_id order by process_time) as prevrow
,lead(product_type_id) over (partition by product_id order by process_time) as nextrow
from products )
where nextrow <> product_type_id or nextrow is null;

commit;

drop table products;

执行我们得到:

Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.

PROCESS_TIME                    PRODUCT_ID PRODUCT_TYPE_ID    PREVROW    NEXTROW
------------------------------- ---------- --------------- ---------- ----------
04-JUL-09 02.08.43.000000 PM             5               4                     4
05-JUL-09 03.08.43.000000 PM             5               4          4          6
06-JUL-09 04.08.43.000000 PM             5               6          4          6
07-JUL-09 04.08.43.000000 PM             5               6          6          4
08-JUL-09 05.08.43.000000 PM             5               4          6          4
08-JUL-09 06.08.43.000000 PM             5               4          4           


6 rows selected.

PROCESS_TIME                    PRODUCT_ID PRODUCT_TYPE_ID    PREVROW    NEXTROW
------------------------------- ---------- --------------- ---------- ----------
05-JUL-09 03.08.43.000000 PM             5               4          4          6
07-JUL-09 04.08.43.000000 PM             5               6          6          4
08-JUL-09 06.08.43.000000 PM             5               4          4           


3 rows selected.
Commit complete.
Table dropped.