如何在不使用预言机中的前导/滞后函数的情况下比较同一表中的两行?
我尝试了表的自连接并使用行号功能,但它对我不起作用。
假设表是 T1,数据以如下所述的格式存储。
ID Name Address Date
1 A Noida 10-Apr-2019
1 A Gurugram 15-Apr-2019
1 A Mumbai 18-Apr-2019
我希望输出数据是这样的。
ID Name Old_value New_value Date
1 A Noida 10-Apr-2019
1 A Noida Gurugram 15-Apr-2019
1 A Gurugram Mumbai 18-Apr-2019
这里有一个选项:
SQL> with test (id, name, address, cdate) as
2 (select 1, 'A', 'Noida' , date '2019-04-10' from dual union all
3 select 1, 'A', 'Gurugram', date '2019-04-15' from dual union all
4 select 1, 'A', 'Mumbai' , date '2019-04-18' from dual
5 ),
6 temp as
7 (select t.*,
8 row_number() over (partition by name order by cdate) rn
9 from test t
10 )
11 select a.id, a.name, b.address old_value, a.address new_value, a.cdate
12 from temp a left join temp b on a.id = b.id and a.rn = b.rn + 1
13 order by a.cdate;
ID N OLD_VALU NEW_VALU CDATE
---------- - -------- -------- ----------
1 A Noida 10.04.2019
1 A Noida Gurugram 15.04.2019
1 A Gurugram Mumbai 18.04.2019
SQL>
如果你不想使用窗口函数,你可以使用子查询来做到这一点:
select
t.ID, t.Name,
(select Address from tablename
where ID = t.ID
and "Date" = (
select max("Date")
from tablename
where ID = t.ID and "Date" < t."Date"
)
) Old_value,
t.Address New_value,
t."Date"
from tablename t
请参阅演示。
结果:
> ID | NAME | OLD_VALUE | NEW_VALUE | Date
> -: | :--- | :-------- | :-------- | :--------
> 1 | A | | Noida | 10-APR-19
> 1 | A | Noida | Gurugram | 15-APR-19
> 1 | A | Gurugram | Mumbai | 18-APR-19
在以后的问题中,我强烈建议:
- 使用 CREATE TABLE 和 INSERT 语句提供测试数据
- 共享精确的 Oracle 数据库版本号。
从版本 12c 开始,您可以使用行模式匹配:
with test (id, name, address, cdate) as
(select 1, 'A', 'Noida' , date '2019-04-10' from dual union all
select 1, 'A', 'Gurugram', date '2019-04-15' from dual union all
select 1, 'A', 'Mumbai' , date '2019-04-18' from dual
)
select * from test
match_recognize(
partition by id, name order by cdate
measures prev(address) old_address
all rows per match
pattern(a)
define a as 1=1
);
ID NAME CDATE OLD_ADDRESS ADDRESS
1 A 2019-04-10 00:00:00 Noida
1 A 2019-04-15 00:00:00 Noida Gurugram
1 A 2019-04-18 00:00:00 Gurugram Mumbai