oracle中左右连接实例,oracle 左连接实例

//数据

A

id name

1 test1

2 test2

3 test3

B

id name code

11 test1 '001'

12 test1 '002'

13 test2 '004'

//结果

a.id b.code

1 '001'

2 '004'

3 null

//方法一：先左连接，在去掉重复的数据

with ta as(

select 1 id,'test1' name from dual union all

select 2,'test2' from dual union all

select 3,'test3' from dual)

,tb as(

select 11 id,'test1' name,'001' code from dual union all

select 12,'test1','002' from dual union all

select 13,'test2','004' from dual)

select tc.id,nvl(tc.code,'null') code

from (

select ta.id,ta.name,tb.code

from ta left join tb

on ta.name=tb.name) tc

where not exists(

select 1

from tb

where tb.name=tc.name

and tb.code < tc.code)

//方法二：先去掉重复的数据，在左连接

select ta.id,b.code

from ta left join

(select *

from tb b1

where not exists(

select *

from tb b2

where b1.name=b2.name

and b1.code > b2.code)) b

on ta.name=b.name

//方法三：左连接，按照name分组，为每一小组的一行返回一个行号，

//然后去行号为1的行

select id,code

from (

select row_number() over (partition by ta.id order by ta.id) rn

,ta.id,tb.code

from ta,tb

where ta.name=tb.name(+))

where rn=1