当前记录集中下一个记录集的ORACLE SQL数据

Question

SELECT
  tsn_ref,
  current_node
FROM rtdev.trip_bodies cnode
WHERE thr_id = 1
ORDER BY arrival_time

上面的查询为我提供了9条记录，每条记录都有2列，这是我想要添加的第3列，它将包含来自后续(时间排序)记录集的“TSN_REF”字段。

因此，产出将遵循以下方针：

+---------+--------------+------+
| TSN_REF | CURRENT_NODE | TSN2 |
+---------+--------------+------+
|       1 |            1 | 2    |
|       2 |            2 | 4    |
|       4 |           10 | 8    |
|       8 |           13 | 13   |
|      13 |           16 | NULL | (no subsequent entry)
+---------+--------------+------+

对于如何修改查询以实现这一点，有什么建议吗？

Answer 1

您可以使用LEAD函数：http://www.oracle-base.com/articles/misc/lag-lead-analytic-functions.php来完成这个任务。

会是这样的(这是未经测试的.)

 SELECT TSN_REF, 
        CURRENT_NODE,
        LEAD(TSN_REF, 1, '') OVER (ORDER BY ARRIVAL_TIME) TSN2
   FROM RTDEV.TRIP_BODIES CNODE 
  WHERE (THR_ID = 1) 
  ORDER BY ARRIVAL_TIME

编辑:按铅定序

更新

看看这个能不能..。

select ts_id,
       tsn_ref,
       current_node,
       case when ts_id = ts_id_next then tsn_ref_next else null end tsn_ref_next 
 from (
   with q as (
      select 1 ts_id, 1 tsn_ref,   1 current_node, sysdate - 20 arrival_time from dual
      union
      select 1 ts_id, 2 tsn_ref,   2 current_node, sysdate - 19 arrival_time from dual
      union
      select 1 ts_id, 4 tsn_ref,  10 current_node, sysdate - 18 arrival_time from dual
      union
      select 1 ts_id, 8 tsn_ref,  13 current_node, sysdate - 17 arrival_time from dual
      union
      select 1 ts_id, 13 tsn_ref, 16 current_node, sysdate - 16 arrival_time from dual
      union
      select 2 ts_id, 1 tsn_ref,   1 current_node, sysdate - 20 arrival_time from dual
      union
      select 2 ts_id, 2 tsn_ref,   2 current_node, sysdate - 19 arrival_time from dual
      union
      select 2 ts_id, 4 tsn_ref,  10 current_node, sysdate - 18 arrival_time from dual
      union
      select 2 ts_id, 8 tsn_ref,  13 current_node, sysdate - 17 arrival_time from dual
      union
      select 2 ts_id, 13 tsn_ref, 16 current_node, sysdate - 16 arrival_time from dual
     )
select ts_id, 
   tsn_ref, 
   current_node,
   lead(tsn_ref, 1, '') over (order by ts_id, arrival_time) tsn_ref_next,
   lag(ts_id, 1, ts_id) over (order by ts_id, arrival_time)    ts_id_next
from q
)

您会想要使用您的桌子，所以如下所示：

select ts_id,
       tsn_ref,
       current_node,
       case when ts_id = ts_id_next then tsn_ref_next else null end tsn_ref_next 
 from (
       select ts_id, 
              tsn_ref, 
              current_node,
              arrival_time,
              lead(tsn_ref, 1, '') over (order by ts_id, arrival_time) tsn_ref_next,
              lag(ts_id, 1, ts_id) over (order by ts_id, arrival_time)    ts_id_next
          FROM RTDEV.TRIP_BODIES CNODE 
         ORDER BY ARRIVAL_TIME  -- this order by may not be needed
     )
  ORDER BY ARRIVAL_TIME