文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle(Beta)语法迁移> 伪列> ROWNUM

ROWNUM

分享
更新时间: 2019/08/09 GMT+08:00
说明:

WHERE子句中ROWNUM的迁移遵循以下规则:

  • 仅迁移最后一个ROWNUM。
  • “ROWNUM >= 某数字或函数”会被迁移为LIMIT 0。
  • 如果ROWNUM和GROUP BY/ORDER BY一起使用,只迁移仅包含单表或单个子查询的FROM子句。

输入:在SELECT列表中使用ROWNUM

SELECT ROWNUM, e.ename,e.empid
  FROM  employees e
WHERE e.deptno = 10;

输出

SELECT ROW_NUMBER() OVER() AS ROWNUM, e.ename,e.empid
  FROM  employees e
WHERE e.deptno = 10;

输入:在WHERE子句中使用ROWNUM

SELECT e.empid, e.ename FROM employees e WHERE ROWNUM < 6;

输出

SELECT e.empid, e.ename FROM employees e LIMIT (6 - 1);

输入:在包含函数的WHERE子句中使用ROWNUM

SELECT X.SOFTASSETS_ID,
                     X.SOFTASSETS_NAME,
                     SOFTASSETS_CLASS,
                     SOFTASSETS_SUBCLASS,
                     PCKG_SPMS_SYSSOFT_MANAGE.func_dic_value(X.SOFTASSETS_CLASS, '6') SOFTASSETS_CLASS_CHN,
                     PCKG_SPMS_SYSSOFT_MANAGE.func_dic_value(X.SOFTASSETS_SUBCLASS, '15') SOFTASSETS_SUBCLASS_CHN,
                     PCKG_SPMS_SYSSOFT_MANAGE.func_dic_value(X.STATUS, '8') STATUS,
                     ROWNUM ROW_ID
                FROM (SELECT DISTINCT SOFTASSETS_ID,
                                      SOFTASSETS_NAME,
                                      SOFTASSETS_CLASS,
                                      SOFTASSETS_SUBCLASS,
                                      STATUS
                        FROM SPMS_SOFT_ASSETS
                       WHERE (I_SOFTNAME IS NULL OR (UPPER(SOFTASSETS_NAME) LIKE ('%' || UPPER(I_SOFTNAME) || '%')))
                         AND SOFTASSETS_CLASS = '002'
                         AND   STATUS = '0'
                         ORDER BY SOFTASSETS_ID DESC
                     ) X
                WHERE ROWNUM < TO_NUMBER (begNum) + TO_NUMBER (fetchNum);

输出

SELECT X.SOFTASSETS_ID,
                     X.SOFTASSETS_NAME,
                     SOFTASSETS_CLASS,
                     SOFTASSETS_SUBCLASS,
                     PCKG_SPMS_SYSSOFT_MANAGE.func_dic_value(X.SOFTASSETS_CLASS, '6') SOFTASSETS_CLASS_CHN,
                     PCKG_SPMS_SYSSOFT_MANAGE.func_dic_value(X.SOFTASSETS_SUBCLASS, '15') SOFTASSETS_SUBCLASS_CHN,
                     PCKG_SPMS_SYSSOFT_MANAGE.func_dic_value(X.STATUS, '8') STATUS,
                     ROW_NUMBER() OVER(ORDER BY SOFTASSETS_ID) ROW_ID
                FROM (SELECT DISTINCT *
                        FROM SPMS_SOFT_ASSETS
                       WHERE (I_SOFTNAME IS NULL OR (UPPER(SOFTASSETS_NAME) LIKE ('%' || UPPER(I_SOFTNAME) || '%')))
                         AND SOFTASSETS_CLASS = '002'
                         AND   STATUS = '0'
                         ORDER BY SOFTASSETS_ID DESC
                     ) X
                LIMIT (TO_NUMBER(begNum)+TO_NUMBER(fetchNum) - 1);

输入:ROWNUM与>=操作符以及GROUP BY子句一起使用

SELECT e.deptno FROM emp e WHERE ROWNUM >= 10 GROUP BY e.deptno;

输出

SELECT e.deptno FROM ( SELECT * FROM emp e LIMIT 0 ) e GROUP BY e.deptno;

输入:ROWNUM与包含附加条件的WHERE子句以及ORDER BY子句一起使用

SELECT M.hiredate ,M.ename 
 FROM ( SELECT ename, hiredate 
            FROM emp 
          ORDER BY hiredate ) M
WHERE M.hiredate > SYSDATE - 10  
  AND ROWNUM < 12  
ORDER BY  M.hiredate;

输出

SELECT M.hiredate ,M.ename 
 FROM ( SELECT * FROM ( SELECT ename, hiredate 
            FROM emp 
          ORDER BY hiredate ) M WHERE M.hiredate > SYSDATE - 10  
  LIMIT (12 - 1) ) M  
ORDER BY  M.hiredate;

输入:ROWNUM与SELECT列表以及包含ORDER BY的 FROM子句一起使用

select a,b from (
         select a, b, rownum row_id from
         (
                   select a,b from t1 order by a
         )x  where rownum < 20
)Y where row_id >=10;

输出

select a,b from (
         select a, b, ROW_NUMBER() OVER(ORDER BY a) row_id from
         (
                   select * from t1 order by a
         )x  LIMIT (20 - 1)
)Y where row_id >=10;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区