如何解决Oracle SQL问题？

Question

select * from(select e.*,dense_rank() over(partition by dept_name order by salary desc) as Top_salaried
from employee e) as B where Top_salaried <= 3;

我有以上查询，从上面每个部门获得前3名薪水的查询是工作良好的PostgreSQL。

当我试图在甲骨文中执行同样的命令时，它会抛出错误。

SQL命令未正确结束

有谁能帮我一下吗?我需要怎么用甲骨文修改它？

Answer 1

在Oracle中，表别名不允许AS关键字(但列别名确实允许，例如as top_salaried)。所以：

SELECT *
  FROM (SELECT e.*,
               DENSE_RANK ()
                  OVER (PARTITION BY dept_name ORDER BY salary DESC)   AS top_salaried
          FROM employee e) b   --> no "as" here
 WHERE top_salaried <= 3;