3,491
社区成员
发帖
与我相关
我的任务
分享
SQL>
SQL> create table A(id int primary key, name varchar(10));
Table created
SQL> create table B(id int, time1 date, id3 int);
Table created
SQL> alter table B add constraint fk_a_b foreign key (id3) references a(id);
Table altered
SQL> begin
2 insert into A values(100, 'xxx');
3 insert into A values(200, 'yyy');
4 insert into A values(300, 'zzz');
5 insert into B values(1,sysdate() - 1, 100);
6 insert into B values(2,sysdate() - 0, 100);
7 insert into B values(3,sysdate() + 1, 100);
8 insert into B values(4,sysdate() - 1, 200);
9 insert into B values(5,sysdate() - 0, 200);
10 end;
11 /
PL/SQL procedure successfully completed
SQL> col id format 99999;
SQL> col id3 format 99999;
SQL> col name format 10;
SQL> with bx as(
2 select B.id,
3 B.time1,
4 B.id3,
5 row_number() over(partition by id3 order by time1 desc) rn
6 from B
7 )
8 select A.id, A.name, bx.id, bx.time1, bx.id3
9 from A
10 left join BX on A.id = BX.id3 and BX.rn = 1;
ID NAME ID TIME1 ID3
----- ---------- ----- ----------- -----
100 xxx 3 2018/10/27 100
200 yyy 5 2018/10/26 200
300 zzz
SQL> drop table B purge;
Table dropped
SQL> drop table A purge;
Table dropped
SQL>