首页 文章

SQL Server：Oracle的前1个查询（11g）而不是12c

提问于
浏览 次
0

这是SQL Server中的单个SQL语句

SELECT 
    a.EMPNUM,
    (SELECT TOP 1 NAMEKO 
     FROM PA0001 
     WHERE COMPCD = a.COMPCD 
       AND EMPNUM = a.LASTUP 
     ORDER BY EDDATE DESC) AS LASTNM,
    (SELECT TOP 1 NAMEKO 
     FROM PA0001 
     WHERE COMPCD = a.COMPCD 
       AND EMPNUM = a.FRSTUP 
     ORDER BY EDDATE DESC) AS FIRSNM
FROM PA0000 a

然后，我尝试将它应用于Oracle 11g（不支持前n个查询的Oracle 12c）

SELECT 
    empnum,  
    (SELECT NAMEKO 
     FROM 
          (SELECT NAMEKO 
           FROM PA0001 
           WHERE COMPCD = a.COMPCD 
             AND EMPNUM = a.FRSTUP 
           ORDER BY EDDATE DESC) 
     WHERE ROWNUM = 1) AS FRSTNM,  
    (SELECT NAMEKO 
     FROM 
          (SELECT NAMEKO 
           FROM PA0001 
           WHERE COMPCD = a.COMPCD 
             AND EMPNUM = a.LASTUP 
           ORDER BY EDDATE DESC) 
     WHERE ROWNUM = 1) AS LASTNM
FROM PA0000 a

然后我收到了一个错误

ORA-00904：“A” . “FRSTUP”：标识符无效 .

如何重写Oracle的查询？

sql-server oracle oracle11g

3 回答

  • 1

    首先，您需要将别名"a"添加到表 PA0000 以避免错误 ORA-00904: "A"."FRSTUP": invalid identifier.

    回复于
  • 1

    使用rownum = 1将选择一个不依赖于order by子句的随机值（而不是块如何在磁盘上对齐）

    12c有此功能（ FETCH FIRST ROWS ONLY 条款）

    在11.2及更少，使用 row_number () 是最方便的方式

    选择在架构中创建的最新对象（单个）的示例：

    select *
  from (select user_objects.object_name,
               user_objects.object_id,
               user_objects.created,
               row_number () over (order by user_objects.created desc) as rn
          from user_objects) view_uo
 where view_uo.rn = 1

    查看最新的10个对象将是 rn <= 10

    然后只需修改它以包含您的选择 .

    回复于
  • 0

    根据我对您问题的理解，您可以使用 level, connect by prior 实现结果，如下所示：

    SELECT EMPNO FROM EMP WHERE HIREDATE IN
(SELECT MAX(HIREDATE) FROM EMP WHERE LEVEL<=1 CONNECT BY PRIOR HIREDATE>HIREDATE GROUP BY LEVEL) ORDER BY HIREDATE DESC;

    希望这能解决您的需求......

    回复于

相关问题