sql-server - 迁移SQL Server将查询应用于Oracle

Oracle中以下Sql Server查询的等效项是什么:

select dd.dname, e.ename
from emp e
outer apply
(select top 1 dname from dept d where d.did=e.did order by bdate) dd

请注意，实际查询有很大不同，但概念相同。请原谅我以上查询中的任何语法错误。

我在下面的Oracle查询中尝试过:

select dd.dname, e.ename
from emp e
left join
(select * from
(select dname from dept d where d.did=e.did order by bdate)
where rownum=1) dd

但是，它给出以下错误:

Error at Command Line:6 Column:18
Error report:
SQL Error: ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:
*Action:

最佳答案

您可以先排列行，然后再排列第一行:

select e.ename, d.dname
  from emp e
  left join  (
      select dept.dname, dept.did, row_number()
         over (partition by did order by bdate) rn from dept) d
    on e.did = d.did and d.rn=1

SQLFiddle

关于sql-server - 迁移SQL Server将查询应用于Oracle，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30801387/