17,377
社区成员
发帖
与我相关
我的任务
分享
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
)
--左连接:
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