mysql - 如何在Oracle中使用limit关键字

我正在从表中检索数据，但不是所有行，而是一次需要20行进行分页。为此，我使用limit关键字，它在Mysql中工作得很好，但在Oracle中没有。
代码：

"select "+  "C.CONTRACTOR_ID,C.CONTRACTOR_NAME,nvl(C.CONTACT_PERSON_1,'-'),nvl(C.CONTACT_PERSON_2,'-'),C.REGISTRATION_NO,CRA.DESCRIPTION REG_AUTH_NAME,"+
            "to_char(C.VALID_FROM,'dd/mm/yyyy'),to_char(C.VALID_TO,'dd/mm/yyyy'),CC.DESCRIPTION CONTRACTOR_CLASS,C.INCORP_PLACE,"+
            "IT.DESCRIPTION INCORP_TYPE,nvl(to_char(C.DATE_OF_INCORP,'dd/mm/yyyy'),'-') DATE_OF_INCORP,C.ADDRESS1,nvl(C.ADDRESS2,'-'),nvl(C.EMAIL_ID,'-'),"+
            "nvl(C.WEBSITE_URL,'-'),nvl(C.PHONE_NO,'-'),nvl(C.FAX_NO,'-'),nvl(C.MOBILE_NO,'-'),C.BANK_NAME,C.BANK_BRANCH,C.ACCOUNT_NO,C.IFSC_CODE," +
            "C.PAN_NO,nvl(C.TIN_NO,'-'),nvl(C.CST_NO,'-') "+
"from "+
            "CONTRACTOR C "+
            "inner join CONTRACTOR_REG_AUTH CRA on CRA.REG_AUTH_ID=C.REG_AUTH_ID "+
            "inner join CONTRACTOR_CLASS CC on CC.CLASS_ID=C.CONTRACTOR_CLASS_ID "+
            "inner join INCORPORATION_TYPE IT on IT.INCORP_TYPE=C.INCORP_TYPE "+
"limit " + offset + ", " + noOfRecords ";

这里没有按关键字排序。我从不同的表中检索数据，然后一次只显示20行。

最佳答案

在Oracle中，可以使用特殊的rownum变量。此示例的行为类似于：

select  *
from    (
        select  *
        ,       rownum as rn
        from    YourTable
        order by
                id
        ) as SubQueryAlias
where   FirstRow <= rn
        and rn < FirstRow + NrOfRows

此查询的优化版本来自AskTom，链接自this SO question：

select  *
from    (
        select  /*+ FIRST_ROWS(n) */
                rownum as rn
        ,       *
        from    (
                select  *
                from    YourTable
                order by
                        id
                ) as SubQueryAlias1
        where   rownum <= FirstRow + NrOfRows
        ) as SubQueryAlias2
where   rn >= FirstRow

关于mysql - 如何在Oracle中使用limit关键字，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11682375/