oracle 左连接实例

屈9561

　　//数据Aid name   1 test12 test23 test3Bid 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 allselect 2,'test2' from dual union allselect 3,'test3' from dual),tb as(select 11 id,'test1' name,'001' code from dual union allselect 12,'test1','002' from dual union allselect 13,'test2','004' from dual)select tc.id,nvl(tc.code,'null') codefrom (select ta.id,ta.name,tb.codefrom ta left join tbon ta.name=tb.name) tcwhere not exists(select 1from tbwhere tb.name=tc.nameand tb.code < tc.code)//方法二：先去掉重复的数据，在左连接select ta.id,b.codefrom ta left join(select *from tb b1where not exists(select *from tb b2where b1.name=b2.nameand b1.code > b2.code)) bon ta.name=b.name//方法三：左连接，按照name分组，为每一小组的一行返回一个行号，//然后去行号为1的行select id,codefrom (select row_number() over (partition by ta.id order by ta.id) rn,ta.id,tb.codefrom ta,tbwhere ta.name=tb.name(+))where rn=1
　　原帖：
　　http://topic.csdn.net/u/20110523/09/8c0a068f-658b-46f2-8dfd-2abb010af963.html?