Oracle获取行，其中列值已更改 [英] Oracle get row where column value changed

问题描述

说我有一张桌子，类似

ID     CCTR    DATE
-----  ------  ----------
1      2C      8/1/2018
2      2C      7/2/2018
3      2C      5/4/2017
4      2B      3/2/2017
5      2B      1/1/2017
6      UC      11/23/2016

还有其他字段，但我使其变得简单.因此，我创建了一个查询，其中我的日期按降序排列.我要返回CCTR发生变化的那一行.因此，在这种情况下，它将返回ID4.基本上，我想在CCTR更改之前找到它的先前值，在这种情况下是从2B到2C.

There are other fields, but I made it simple. So I create a query where i have the date in descending order. I was to return the row where there was a change in CCTR. So in this case it would return ID 4. Basically i want to find the previous value of CCTR before it changed, in this case from 2B to 2C.

我该怎么做?香港专业教育学院试图谷歌它，但似乎找不到正确的方法.

How do I do this? Ive tried to google it, but can't seem to find the right method.

推荐答案

您可以使用LAG()窗口函数来窥视上一行并进行比较.如果您的数据是:

You can use the LAG() window function to peek at the previous row and compare it. If your data is:

create table t2 (
  id number(6),
  cctr varchar2(10),
  date1 date
);

insert into t2 (id, cctr, date1) values (1, '2C', date '2018-08-01');
insert into t2 (id, cctr, date1) values (2, '2C', date '2018-07-02');
insert into t2 (id, cctr, date1) values (3, '2C', date '2017-05-04');
insert into t2 (id, cctr, date1) values (4, '2B', date '2017-03-02');
insert into t2 (id, cctr, date1) values (5, '2B', date '2017-01-01');
insert into t2 (id, cctr, date1) values (6, 'UC', date '2016-11-23');

然后查询将是:

select * from t2 where date1 = (
  select max(date1)
    from (
    select 
      id, date1, cctr, lag(cctr) over(order by date1 desc) as prev
      from t2
    ) x  
    where prev is not null and cctr <> prev
);

结果:

ID       CCTR        DATE1    
-------  ----------  -------------------
4        2B          2017-03-02 00:00:00  

这篇关于Oracle获取行，其中列值已更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！