日期:2014-05-17 浏览次数:20808 次
SELECT T1.A_ID,NVL(T2.B_NAME,'') AS B_NAME1,NVL(T3.B_NAME,'') AS B_NAME2,T1.ATYPE FROM A T1 LEFT JOIN B T2 ON T1.B_ID1 = T2.B_ID LEFT JOIN B T3 ON T1.B_ID2 = T3.B_ID ORDER BY T1.A_ID
with a as(
select 1 a_id,1 b_id1,2 b_id2,1 atype from dual
union all
select 2,1,3,2 from dual
union all
select 3,null,null,3 from dual
union all
select 4,null,2,4 from dual
union all
select 5,null,null,5 from dual
union all
select 6,4,null,6 from dual
),b as(
select 1 b_id,'LIU' b_name from dual
union all
select 2,'KAI' from dual
union all
select 3,'AAA' from dual
union all
select 4,'BBB' from dual
union all
select 5,'CCC' from dual
)
SELECT A_ID,(SELECT B_NAME FROM B WHERE B_ID=A.B_ID1) B_ID1,(SELECT B_NAME FROM B WHERE B_ID=A.B_ID2) B_ID2,ATYPE FROM A
A_ID B_ID1 B_ID2 ATYPE
---------- ----- ----- ----------
1 LIU KAI 1
2 LIU AAA 2
3 3
4 KAI 4
5 5
6 BBB 6