Hi-StackoverFlow社区，
我想在Oracle SQL 中执行以下操作
查询的表看起来像：
M_id s_s_u s_e_u a_s_t e_i sr1
K1 2020-02-19 13:00 2020-02-19 13:10 MS P12 CI
K1 2020-02-19 13:10 2020-02-19 13:17 MS P11 CI
K2 2020-02-19 13:04 2020-02-19 13:14 MT P13 CD
K2 2020-02-19 13:14 2020-02-19 13:23 MT P18 CI
我想做的事情如下。
我每隔几个小时加载一次数据。在一个单独的表中，我记录每个M_id的最后一行。
因此，对于上面的示例，我的表(在表中，我为每次数据加载的每个m_id插入最后一行(将具有以下记录。
最后记录
M_id s_e_u a_s_t e_i sr1
K1 2020-02-19 13:17 MS P12 CI
K2 2020-02-19 13:23 MS P12 CI
我成功地从MSSQL中获得了行，但我很难定义要从中加载查询的第一行。需要继续数据流，这就是为什么最后记录的s_e_u=下一记录的s_Su。
在这篇文章中，创建第一个表的当前查询相当长，如果需要，我可以分享它。
如果你能把我推向一个解决方案的方向，在那里我可以使用表last_records的记录作为实际查询的起点，那就太好了。
声音就像一个缺口和孤岛问题。如果你正在寻找我认为的东西，你应该能够使用match_recognize；不需要中间最后记录表。
类似这样的东西：
WITH test_data AS (
select 'K1' as M_id,to_date('02/19/2020 13:00:00','mm/dd/yyyy hh24:mi:ss') as s_s_u,to_date('02/19/2020 13:10:00','mm/dd/yyyy hh24:mi:ss') as s_e_u,'MS' as a_s_t,'P12' as e_i,'CI' as sr1 from dual union all
select 'K1' as M_id,to_date('02/19/2020 13:10:00','mm/dd/yyyy hh24:mi:ss') as s_s_u,to_date('02/19/2020 13:17:00','mm/dd/yyyy hh24:mi:ss') as s_e_u,'MS' as a_s_t,'P11' as e_i,'CI' as sr1 from dual union all
select 'K2' as M_id,to_date('02/19/2020 13:04:00','mm/dd/yyyy hh24:mi:ss') as s_s_u,to_date('02/19/2020 13:14:00','mm/dd/yyyy hh24:mi:ss') as s_e_u,'MT' as a_s_t,'P13' as e_i,'CD' as sr1 from dual union all
select 'K2' as M_id,to_date('02/19/2020 13:14:00','mm/dd/yyyy hh24:mi:ss') as s_s_u,to_date('02/19/2020 13:23:00','mm/dd/yyyy hh24:mi:ss') as s_e_u,'MT' as a_s_t,'P18' as e_i,'CI' as sr1 from dual
)
SELECT m.m_id,
m.match_overall_s_s_u,
m.match_overall_s_e_u
FROM test_data c
match_recognize(
PARTITION BY m_id
ORDER BY s_s_u
MEASURES
FIRST(s_s_u) AS match_overall_s_s_u,
LAST(s_e_u) AS match_overall_s_e_u
pattern (
anything
connected_val*
) define
connected_val AS s_s_u = prev(s_e_u) /* Record is connected with the previous if its start equals the previous record's end */
) m
如果我理解正确，你似乎想要这样的东西：
select t1.*
from table1 t1
where t1.s_s_u > (select max(t2.s_s_u) from table2 t2 where t2.m_id = t.m_id)