insert into A as select * from B where ... 不能有order by 子句
可变通为
create view C as select * from B where ... Order by ...
然后 insert into A as select * from C;
可变通为
create view C as select * from B where ... Order by ...
然后 insert into A as select * from C;
SQL> CREATE TABLE AA(AA VARCHAR(10),BB VARCHAR(10));表已创建。SQL> INSERT INTO AA(AA,BB) VALUES('1','NNSO');已创建 1 行。SQL> INSERT INTO AA(AA.BB) VALUES('3','HH');
INSERT INTO AA(AA.BB) VALUES('3','HH')
*
ERROR 位于第 1 行:
ORA-00913: 值过多
SQL> INSERT INTO AA(AA,BB) VALUES('3','HH');已创建 1 行。SQL> INSERT INTO AA(AA,BB) VALUES('2','D');已创建 1 行。SQL> COMMIT;提交完成。SQL> SELECT * FROM AA;AA BB
-------------------- --------------------
1 NNSO
3 HH
2 DSQL> CREATE TABLE BB AS SELECT * FROM AA ORDER BY AA;表已创建。SQL> SELECT * FROM BB;AA BB
-------------------- --------------------
1 NNSO
2 D
3 HHSQL> DELETE TABLE BB;
DELETE TABLE BB
*
ERROR 位于第 1 行:
ORA-00903: 表名无效
SQL> DELETE BB;已删除3行。SQL> INSERT INTO BB SELECT * FROM AA ORDER BY AA;已创建3行。SQL> COMMIT;提交完成。SQL> SELECT * FROM BB;AA BB
-------------------- --------------------
1 NNSO
2 D
3 HH