选择前两个不同的匹配项并返回 rowid

Question

>我有这样的表格：

room_id | name    | time
1       | Kate    | 2019-09-18 10:00:00.000
1       | Michael | 2019-09-18 12:00:00.000
1       | George  | 2019-09-18 14:00:00.000
2       | Tom     | 2019-09-17 09:00:00.000
2       | Ben     | 2019-09-17 15:00:00.000
3       | Joerge  | 2019-09-16 17:00:00.000

我想选择前 N 个不同的room_ids和最后一个row_id。 例如，我想选择前两个不同的rooms_ids并返回最后一个row_id。结果应该是：

room_id
1
2

应使用此记录标识row_id：

2       | Ben     | 2019-09-17 15:00:00.000

我已经写了我的SQL语句，但它不起作用：

SELECT distinct room_id
FROM (
SELECT DISTINCT room_id, time,
rn = ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY room_id, time)
FROM tab
) AS sub
WHERE rn <= N;

"N"是我要选择多少个不同room_ids的值。 我也不知道如何返回最后一条记录的row_id。

Answer 1

你可以这样尝试：

with t1 as
(
select t.*,
row_number() over (partition by room_id order by room_id, time desc) as rn
from tab t
) 
select room_id, name, time
from t1
where rn = 1 and room_id = N

演示

重要的关注点是考虑row_number()分析函数中的order by time desc，并获取外部查询的rn = 1。

Answer 2

这里有一个选项：

SQL>  set ver off
SQL>
SQL> with test (room_id, name, time) as
2    (select 1, 'Kate'   , to_date('18.09.2019 10:00', 'dd.mm.yyyy hh24:mi') from dual union all
3     select 1, 'Michael', to_date('18.09.2019 12:00', 'dd.mm.yyyy hh24:mi') from dual union all
4     select 1, 'George' , to_date('18.09.2019 14:00', 'dd.mm.yyyy hh24:mi') from dual union all
5     select 2, 'Tom'    , to_date('17.09.2019 09:00', 'dd.mm.yyyy hh24:mi') from dual union all
6     select 2, 'Ben'    , to_date('17.09.2019 15:00', 'dd.mm.yyyy hh24:mi') from dual union all
7     select 3, 'Joerge' , to_date('16.09.2019 17:00', 'dd.mm.yyyy hh24:mi') from dual
8    ),
9  rn_room as
10    (select room_id,
11            row_number() over (order by room_id) rnr
12     from (select distinct room_id from test)
13    ),
14  rn_time as
15    (select room_id, name, time,
16            row_number() over (partition by room_id order by time desc) rnt
17     from test
18    )
19  select r.room_id, t.name, t.time
20  from rn_time t join rn_room r on r.room_id = t.room_id
21  where r.rnr <= &N
22    and t.rnt = 1;
Enter value for n: 2
ROOM_ID NAME    TIME
---------- ------- ----------------
1 George  2019-09-18 14:00
2 Ben     2019-09-17 15:00
SQL>

• rn_room对房间进行分类
• rn_time按时间对每个房间的名称进行排序
• 最终select连接这两个数据集