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

原帖：

http://topic.youkuaiyun.com/u/20110523/09/8c0a068f-658b-46f2-8dfd-2abb010af963.html?