--左连接: SQL> with ta as( 2 select 1 dm,'ss' mc from dual union all 3 select 2,'dd' from dual) 4 ,tb as( 5 select 1 pkid,1 dm,'kk' gyr from dual) 6 select ta.dm,ta.mc,tb.pkid,tb.gyr 7 from ta,tb 8 where ta.dm=tb.dm(+) 9 /
DM MC PKID GYR ---------- -- ---------- --- 1 ss 1 kk 2 dd
CREATE OR REPLACE VIEW V_TABLE1_TABLE2 (DM,MC,PKID,GYR) AS ( SELECT * FROM (SELECT * FROM (WITH TABLE1 AS (SELECT '1' DM, 'SS' MC FROM DUAL UNION ALL SELECT '2' DM, 'DD' MC FROM DUAL) SELECT * FROM TABLE1) ) T1 LEFT JOIN (SELECT DM, MC FROM (WITH TABLE2 AS (SELECT '1' PKID, '1' DM, 'KK' MC FROM DUAL) SELECT * FROM TABLE2) ) T2 ON T1.DM = T2.DM )
CREATE OR REPLACE VIEW V_TABLE1_TABLE2 (DM,MC,PKID,GYR) AS ( select ta.dm,ta.mc,tb.pkid,tb.gyr from ta,tb where ta.dm=tb.dm(+) )
--左连接:
SQL> with ta as(
2 select 1 dm,'ss' mc from dual union all
3 select 2,'dd' from dual)
4 ,tb as(
5 select 1 pkid,1 dm,'kk' gyr from dual)
6 select ta.dm,ta.mc,tb.pkid,tb.gyr
7 from ta,tb
8 where ta.dm=tb.dm(+)
9 /
DM MC PKID GYR
---------- -- ---------- ---
1 ss 1 kk
2 dd
CREATE OR REPLACE VIEW V_TABLE1_TABLE2 (DM,MC,PKID,GYR) AS (
SELECT *
FROM (SELECT *
FROM (WITH TABLE1 AS (SELECT '1' DM, 'SS' MC
FROM DUAL
UNION ALL
SELECT '2' DM, 'DD' MC FROM DUAL)
SELECT * FROM TABLE1)
) T1
LEFT JOIN (SELECT DM, MC
FROM (WITH TABLE2 AS (SELECT '1' PKID, '1' DM, 'KK' MC
FROM DUAL)
SELECT * FROM TABLE2)
) T2
ON T1.DM = T2.DM
)
select ta.dm,ta.mc,tb.pkid,tb.gyr
from ta,tb
where ta.dm=tb.dm(+)
)