主页 / 问题 / 1301307
Accepted
Gipnomanul
Asked:2022-07-02 20:00:51 +0000 UTC2022-07-02 20:00:51 +0000 UTC

显示有记录的最后一次飞行

  • 772

有一张火车时刻表：

create table atest (
TR_ID NUMBER(9),
TR_START DATE,
TR_END DATE,
TR_FROMSTART VARCHAR2(255),
TR_FROMEND VARCHAR2(255) )

TR_ID   TR_START              TR_END             TR_FROMSTART TR_FROMEND 
    1   01.06.2020 4:50:00    01.06.2020 7:50:00       MSK         KZN  
    1   01.06.2020 9:35:00    01.06.2020 13:00:00      КZN         ANP  
    2   03.06.2020 15:35:00   03.06.2020 18:30:00      MSK         SPB  
    2   05.06.2020 5:15:00    05.06.2020 10:30:00      SPB         MSK  
    3   05.06.2020 12:05:00   05.06.2020 12:05:00      MSK         NSB  
    3   08.06.2020 17:05:00   09.06.2020 1:50:00       NSB         MSK  
    1   10.06.2020 12:30:00   10.06.2020 16:00:00      ANP         KZN  
    1   12.06.2020 17:20:00   13.06.2020 5:35:00       KZN         MSK  
    2   15.06.2020 5:15:00    15.06.2020 10:30:00      MSK         ANP  
    2   15.06.2020 12:05:00   15.06.2020 17:50:00      ANP         KZN

表中的列负责以下值：TR_ID- 火车 ID，TR_START и TR_END- 火车出发和到达时间，TR_FROMSTART и TR_FROMEND- 火车出发和到达点。

任务如下： 创建一个查询，将显示相同的列，但添加列有这列火车上一个航班的出发和到达时间，航班必须在前一天或更早。

应该发生的事情的一个例子：

      TR_ID  TR_START                TR_END             TR_FROMSTART TR_FROMEND       LASTRACE_ST 
            1   01.06.2020 4:50:00    01.06.2020 7:50:00       MSK         KZN             0  
            1   01.06.2020 9:35:00    01.06.2020 13:00:00      КZN         ANP           01.06.2020 4:50:00   
            2   03.06.2020 15:35:00   03.06.2020 18:30:00      MSK         SPB             0
            2   05.06.2020 5:15:00    05.06.2020 10:30:00      SPB         MSK           05.06.2020 5:15:00   
            3   05.06.2020 12:05:00   05.06.2020 12:05:00      MSK         NSB             0
            3   08.06.2020 17:05:00   09.06.2020 1:50:00       NSB         MSK           08.06.2020 17:05:00 
.................................................... и т.д.

尝试以多种方式完成任务，最接近的是NTH_VALUE：

select distinct  a.tr_id, trunc(a.tr_start), a.tr_fromstart, a.tr_fromend,
NTH_VALUE(a.tr_start, 2) OVER (PARTITION BY a.tr_id ORDER BY a.tr_start DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lastrace_st,
NTH_VALUE(a.tr_end, 2) OVER (PARTITION BY a.tr_id ORDER BY a.tr_start DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lastrace_end 
       from atest a
order by trunc(a.tr_start)

但是，数据选择不正确 - 整个火车上的倒数第二个行程被采取，而不是前一个。

sql

1 个回答

  1. Best Answer

    试试这样（在db<>fiddle上）：

    select * 
from atest t1
outer apply (
    select 
        max (tr_start) keep (dense_rank last order by t2.tr_start asc) last_start,
        max (tr_end)   keep (dense_rank last order by t2.tr_end   asc) last_end
    from atest t2
    where t2.tr_id  = t1.tr_Id 
    and   t2.tr_fromend = t1.tr_fromstart  
    and   t2.tr_end < t1.tr_start
)
order by t1.tr_start

    结果：

    TR_ID TR_START         TR_END           DEP ARR LAST_START       LAST_END        
----- ---------------- ---------------- --- --- ---------------- ----------------
    1 01.06.2020 04:50 01.06.2020 07:50 MSK KZN                                  
    1 01.06.2020 09:35 01.06.2020 13:00 KZN ANP 01.06.2020 04:50 01.06.2020 07:50
    2 03.06.2020 15:35 03.06.2020 18:30 MSK SPB                                  
    2 05.06.2020 05:15 05.06.2020 10:30 SPB MSK 03.06.2020 15:35 03.06.2020 18:30
    3 05.06.2020 12:05 05.06.2020 12:05 MSK NSB                                  
    3 08.06.2020 17:05 09.06.2020 01:50 NSB MSK 05.06.2020 12:05 05.06.2020 12:05
    1 10.06.2020 12:30 10.06.2020 16:00 ANP KZN 01.06.2020 09:35 01.06.2020 13:00
    1 12.06.2020 17:20 13.06.2020 05:35 KZN MSK 10.06.2020 12:30 10.06.2020 16:00
    2 15.06.2020 05:15 15.06.2020 10:30 MSK ANP 05.06.2020 05:15 05.06.2020 10:30
    2 15.06.2020 12:05 15.06.2020 17:50 ANP KZN 15.06.2020 05:15 15.06.2020 10:30

    由于描述与预期结果不完全匹配，因此有几个解释：

    • t2.tr_fromend = t1.tr_fromstart- 航班必须首先到达下一航班出发的同一站
    • t2.tr_end < t1.tr_start- 航班可立即前往下一目的地站；条件：航班必须在前一天或更早，必须替换为t2.tr_end < trunc (t1.tr_start)
    • 3

相关问题