sql - 给定sql中的起点和终点，如何从表中获取一组记录？

Question

我有 2 个表结构：

TAB_1 ( TRN_ID , STN_CODE,DIST_FRM_SRC)    

TAB_2 ( TRN_ID , SRC_STN , DSTN_STN ) 

TAB_1 中的数据：

TAB_2 中的数据

我必须从 TAB_1 中获取与 TAB_2 中的 SRC_STN 和 DST_STN 相对应的所有行，所以 OP 将是

请指导。

标签： sqloracle

Answer 1

看起来像一个简单的连接：

SQL> with
  2  tab_1 (trn_id, stn_code, dist_frm_src) as
  3    (select 100, 'GHU', 0 from dual union all
  4     select 100, 'SDP', 2 from dual union all
  5     select 100, 'DRK', 5 from dual union all
  6     select 100, 'SAB', 7 from dual union all
  7     select 100, 'DRT', 8 from dual union all
  8     select 100, 'POL', 10 from dual union all
  9     select 100, 'WRT', 15 from dual),
 10  tab_2 (trn_id, src_stn, dstn_stn) as
 11    (select 100, 'SDP', 'POL' from dual),
 12  --
 13  dfs as
 14    (select x.trn_id,
 15            a.dist_frm_src val_1,
 16            b.dist_frm_src val_2
 17     from tab_2 x join tab_1 a on x.trn_id = a.trn_id and x.src_stn = a.stn_code
 18                  join tab_1 b on x.trn_id = b.trn_id and x.dstn_stn = b.stn_code
 19    )
 20  select a.trn_id, a.stn_code, a.dist_frm_src
 21  from tab_1 a join dfs d on a.trn_id = d.trn_id
 22                         and a.dist_frm_src between d.val_1 and d.val_2
 23  order by trn_id, a.dist_frm_src;

    TRN_ID STN DIST_FRM_SRC
---------- --- ------------
       100 SDP            2
       100 DRK            5
       100 SAB            7
       100 DRT            8
       100 POL           10

SQL>