oracle外连接查询( ),外连接与子查询（的Oracle 11g）

这里有几个例子是应该可以帮助你实现你以后(我做了我自己的表/因为你的数据拒绝提供样本数据，但原理是一样的，你应该能够应用它自己的查询)：

例1(使用LEFT JOIN和分析功能)：

with t1 as (select 1 id, to_date('01/01/2016', 'dd/mm/yyyy') dt, 'a' val from dual union all

select 2 id, to_date('02/03/2016', 'dd/mm/yyyy') dt, 'b' val from dual union all

select 3 id, to_date('03/02/2016', 'dd/mm/yyyy') dt, 'c' val from dual union all

select 4 id, to_date('04/01/2016', 'dd/mm/yyyy') dt, 'd' val from dual),

t2 as (select 1 id, 100 val, to_date('01/12/2015', 'dd/mm/yyyy') dt from dual union all

select 1 id, 120 val, to_date('12/12/2015', 'dd/mm/yyyy') dt from dual union all

select 1 id, 130 val, to_date('04/01/2016', 'dd/mm/yyyy') dt from dual union all

select 2 id, 200 val, to_date('01/03/2016', 'dd/mm/yyyy') dt from dual union all

select 3 id, 300 val, to_date('04/03/2016', 'dd/mm/yyyy') dt from dual union all

select 3 id, 330 val, to_date('06/03/2016', 'dd/mm/yyyy') dt from dual)

-- end of mimicking two tables, t1 and t2, containing data. See SQL below:

select id,

t1_dt,

t1_val,

t2_val

from (select t1.id,

t1.dt t1_dt,

t1.val t1_val,

t2.val t2_val,

t2.dt t2_dt,

row_number() over (partition by t1.id order by t2.dt desc) rn

from t1

left outer join (select id,

val,

dt

from t2) t2 on (t1.id = t2.id and t2.dt <= t1.dt))

where rn = 1;

ID T1_DT T1_VAL T2_VAL

---------- ---------- ------ ----------

1 01/01/2016 a 120

2 02/03/2016 b 200

3 03/02/2016 c

4 04/01/2016 d

例2(使用标量子查询)：

with t1 as (select 1 id, to_date('01/01/2016', 'dd/mm/yyyy') dt, 'a' val from dual union all

select 2 id, to_date('02/03/2016', 'dd/mm/yyyy') dt, 'b' val from dual union all

select 3 id, to_date('03/02/2016', 'dd/mm/yyyy') dt, 'c' val from dual union all

select 4 id, to_date('04/01/2016', 'dd/mm/yyyy') dt, 'd' val from dual),

t2 as (select 1 id, 100 val, to_date('01/12/2015', 'dd/mm/yyyy') dt from dual union all

select 1 id, 120 val, to_date('12/12/2015', 'dd/mm/yyyy') dt from dual union all

select 1 id, 130 val, to_date('04/01/2016', 'dd/mm/yyyy') dt from dual union all

select 2 id, 200 val, to_date('01/03/2016', 'dd/mm/yyyy') dt from dual union all

select 3 id, 300 val, to_date('04/03/2016', 'dd/mm/yyyy') dt from dual union all

select 3 id, 330 val, to_date('06/03/2016', 'dd/mm/yyyy') dt from dual)

-- end of mimicking two tables, t1 and t2, containing data. See SQL below:

select id,

dt t1_dt,

val t1_val,

(select max(val) keep (dense_rank first order by t2.dt desc) max_val

from t2

where t1.id = t2.id

and t2.dt <= t1.dt) t2_val

from t1;

ID T1_DT T1_VAL T2_VAL

---------- ---------- ------ ----------

1 01/01/2016 a 120

2 02/03/2016 b 200

3 03/02/2016 c

4 04/01/2016 d

注：我假设t1.id是唯一的。