我需要通过来自不同模式的两个表的左联接语句在数据库中创建一个新表。下面是我的代码:
CREATE TABLE NEW_TABLE
FROM SCHEMA_1.TABLE_A X
LEFT JOIN SCHEMA_2.TABLE_B Y
ON X.NAME = Y.NAME
AND X.NUMBER = Y.NUMBER
我得到一个SQL错误:
ORA-00922:选项缺失或无效
00922.00000-";缺少或无效的选项";
您缺少as select
子句:
CREATE TABLE NEW_TABLE
AS ( -- This was missing
SELECT * -- So was this
FROM SCHEMA_1.TABLE_A X
LEFT JOIN SCHEMA_2.TABLE_B Y ON X.NAME = Y.NAME AND
X.NUMBER = Y.NUMBER
)
不要将NUMBER
用作列名,它是oracle关键字。
进入查询,如果两个表中唯一常见的列是NAME
、NUMBER
,那么您可以尝试这个
CREATE TABLE NEW_TABLE
AS (
SELECT *
FROM SCHEMA_1.TABLE_A X
LEFT JOIN SCHEMA_2.TABLE_B Y Using(NAME,NUMBER)
)