多次遇到有网友问这个问题。我们来测试下
测试语句为
DROP TABLE emp2 PURGE;
CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;
INSERT INTO emp2
SELECT * FROM (
WITH old_emp AS (SELECT * FROM emp)
SELECT * FROM old_emp
)
下面是测试结果
SQL> DROP TABLE emp2 PURGE;
Table dropped
SQL> CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;
Table created
SQL> INSERT INTO emp2
2 SELECT * FROM (
3 WITH old_emp AS (SELECT * FROM emp)
4 SELECT * FROM old_emp
5 )
6 /
14 rows inserted
SQL>
可见是能用with的,只是需要一点小技巧而已