sql - 从oracle中的多行表中检索行

Question

我想从三个表中检索数据

例如

表_1：NAME_A

PD_ID
 一个
 乙
 C

表_2：NAME_B Primary_key PD_ID，EV_N

PD_ID EV_N EV_DEC
 一个1个
 一二二
 B 1 个
 B 2 两个
 B 3 三
 C 1 个
 C 2 两个

表_3：NAME_C 主键 PD_ID

PD_ID、FFT_NAME、FFT_DESC
A XY XY_DESC
B ZY ZY_DESC
B XY XY_DESC
C ZY ZY_DESC
C XY XY_DESC
C PY PY_DESC

所需输出

PD_ID EV_N EV_DEC FFT_NAME FFT_DESC
 A 1 一个 XY XY_DESC
 一二二       
 B 1 一个 ZY ZY_DESC
 B 2 两个 XY XY_DESC
 B 3 三    
 C 1 一个 ZY ZY_DESC
 C 2 两个 XY XY_DESC
                          PY PY_DESC

标签： sqloracle

Answer 1

这个想法是对两个表中的记录进行范围，然后在完全外部连接中使用这个范围号：

with
t1 as (
  select 'A' pd_id from dual union all
  select 'B' pd_id from dual union all
  select 'C' pd_id from dual
),
t2 as (
  select 'A' pd_id, 1 EV_N, 'one' EV_DEC from dual union all
  select 'A' pd_id, 2 EV_N, 'two' EV_DEC from dual union all
  select 'B' pd_id, 1 EV_N, 'one' EV_DEC from dual union all
  select 'B' pd_id, 2 EV_N, 'two' EV_DEC from dual union all
  select 'B' pd_id, 3 EV_N, 'three' EV_DEC from dual union all
  select 'C' pd_id, 1 EV_N, 'one' EV_DEC from dual union all
  select 'C' pd_id, 2 EV_N, 'two' EV_DEC from dual
),
t3 as (
  select 'A' pd_id, 'XY' FFT_NAME, 'XY_DESC' FFT_DESC from dual union all
  select 'B' pd_id, 'ZY' FFT_NAME, 'ZY_DESC' FFT_DESC from dual union all
  select 'B' pd_id, 'XY' FFT_NAME, 'XY_DESC' FFT_DESC from dual union all
  select 'C' pd_id, 'ZY' FFT_NAME, 'ZY_DESC' FFT_DESC from dual union all
  select 'C' pd_id, 'XY' FFT_NAME, 'XY_DESC' FFT_DESC from dual union all
  select 'C' pd_id, 'PY' FFT_NAME, 'PY_DESC' FFT_DESC from dual
)
select coalesce(t22.pd_id,t33.pd_id) pd_id, 
       t22.ev_dec, 
       t33.FFT_NAME, 
       t33.FFT_DESC
from   (
        select pd_id, ev_n, ev_dec, row_number() over (partition by pd_id order by ev_n, ev_dec) rn
        from  t2
       ) t22 
         full join (
           select pd_id, FFT_NAME, FFT_DESC, row_number() over (partition by pd_id order by FFT_NAME, FFT_DESC) rn
           from   t3
         ) t33 
           on t22.pd_id = t33.pd_id
           and t22.rn = t33.rn