Leetcode Oracle-ORA-00923:在预期位置找不到FROM关键字

Question

我正试图使用Oracle SQL解决这个问题，但我一直收到这个错误-

ORA-00923: FROM keyword not found where expected

问题链接-https://leetcode.com/problems/department-top-three-salaries/submissions/

到目前为止我的解决方案-只是查询数据-

with temp as
(
select d.Name as Department,
e.Name as Employee,
e.Salary as Salary
from employee e
join department d
on e.DepartmentId = d.Id
)
select *
, rank() over (partition by department order by salary desc) as rr
from temp 

但如果我只是运行这个，那么它工作得很好-

with temp as
(
select d.Name as Department,
e.Name as Employee,
e.Salary as Salary
from employee e
join department d
on e.DepartmentId = d.Id
)
select *
from temp 

如果我运行这个，那么它运行正常-

select department, employee, salary
from
(
select A.* , dense_rank() over (partition by department order by salary desc) as rr
from
(
select d.Name as Department,
e.Name as Employee,
e.Salary as Salary
from employee e
join department d
on e.DepartmentId = d.Id
) A
) B
where rr <= 3

这是否意味着我不能在oracle中使用级联的with语句？

例如，我不能写吗-

with temp as 
(
select col1, col2 from table
)
, temp1 as
(
select *, "hello" as col3
from temp
)
select *
from temp1

在Oracle中？

Answer 1

如果只需要选择所有列，那么不带别名的*就可以了。但是，如果您需要在任何您想要使用*和SELECT子句中的另一个表达式来选择表的所有列的地方提供表的别名。

with TEMP AS 
( SELECT
COL1,
COL2
FROM table )
, TEMP1 AS 
(SELECT
T.*, -- alias here
"hello"   AS COL3
FROM TEMP T
)
select * FROM TEMP1;
with temp as
(
select d.Name as Department,
e.Name as Employee,
e.Salary as Salary
from employee e
join department d
on e.DepartmentId = d.Id
)
select T.* -- alias here
, rank() over (partition by department order by salary desc) as rr
from temp T;