[求组]db2中的with ... as (...) 在oracle中应该如何表示

[dunerunner]

如题
在db2中

WITH tmp (xxx, xxx, ...) AS (
     SELECT ... FROM ...
)
SELECT * FROM tmp;

这种用法在oracle中应该怎么做？

[dunerunner]

WITH tmp (xxx, xxx, ...) AS (
     SELECT ... FROM ...
), othertmp(xxx, xxx, ...) AS (
     SELECT ... FROM ...
)
SELECT * FROM othertmp;

[CT_LXL]

引用 1 楼 dunerunner 的回复:

WITH tmp (xxx, xxx, ...) AS (
     SELECT ... FROM ...
), othertmp(xxx, xxx, ...) AS (
     SELECT ... FROM ...
)
SELECT * FROM othertmp;

with t1 as
 (select * from scott.emp),
t2 as
 (select * from scott.dept)
select * from t1, t2 where t1.deptno = t2.deptno;