我确实需要帮助在ORACLE中编写SQL查询下面的样本数据

Question

数据如下:

表

REF_NUM ID  DATE
SIM1    1   12-Oct-22
SIM1    2   10-Oct-22
SIM2    3   15-Oct-22
SIM2    4   14-Oct-22
SIM3    5   08-Oct-22
SIM3    6   02-Oct-22
SIM4    7   08-Oct-22
SIM4    8   10-Oct-22

输出如下:

输出:

REF_NUM ID  DATE
SIM1    2   10-Oct-22
SIM2    4   14-Oct-22
SIM3    6   02-Oct-22
SIM4    7   08-Oct-22

基本上我需要不同的ref_num，各自的ID和第二高日期的数据。这里我只给出了主表中的两个日期，但是每个ref_num可以有两个以上的日期。

我敢肯定无论我做了什么都是错的

Answer 1

按日期数据类型值从小到大排列每个ref_num的行;然后取出排名第二高的。

样本数据:

SQL> with test (ref_num, id, datum) as
2    (select 'sim1', 1, date '2022-10-12' from dual union all
3     select 'sim1', 2, date '2022-10-10' from dual union all
4     select 'sim2', 3, date '2022-10-15' from dual union all
5     select 'sim2', 4, date '2022-10-14' from dual union all
6     select 'sim3', 5, date '2022-10-08' from dual union all
7     select 'sim3', 6, date '2022-10-02' from dual union all
8     select 'sim4', 7, date '2022-10-08' from dual union all
9     select 'sim4', 8, date '2022-10-10' from dual
10    ),

查询从这里开始:

11  temp as
12    (select ref_num, id, datum,
13       rank() over (partition by ref_num order by datum desc) rnk
14     from test
15    )
16  select ref_num, id, datum
17  from temp
18  where rnk = 2
19  order by ref_num;
REF_         ID DATUM
---- ---------- ----------
sim1          2 10.10.2022
sim2          4 14.10.2022
sim3          6 02.10.2022
sim4          7 08.10.2022
SQL>