如何从oracle中的表中选择甚至记录? [英] how to select even records from a table in oracle?

问题描述

我正在使用下面的查询来检索偶数记录.但是在重排时，它不显示任何行

i'm using below query to retrieve even numbered records.but in reslut it is displaying no rows

select ename,job from emp where mod(rownum,2)=0;

为什么mod(rownum，2)在有条件的地方不起作用

why mod(rownum,2) is not working in where conditiom

请问您是否还可以选择奇数记录

can u please also give the query to select the odd number records

该查询有什么问题吗?

建议.

推荐答案

它不起作用，因为:对于第一行，ROWNUM是1，在这种情况下，MOD(ROWNUM,2)是1，并且由于WHERE语句为MOD(ROWNUM,2)=0，然后减少为1=0，并且该行被丢弃.然后将对下一行的ROWNUM进行1测试(因为前一行不再在输出中并且没有行号)，这将再次使测试失败并被丢弃.重复一次，请注意，所有行都不能通过WHERE测试，并被丢弃.

It is not working because: for the first row ROWNUM is 1 and, in this case, MOD(ROWNUM,2) is 1 and since your WHERE statement is MOD(ROWNUM,2)=0 then this reduces to 1=0 and the row is discarded. The subsequent row will then be tested against a ROWNUM of 1 (since the previous row is no longer in the output and will not have a row number), which will again fail the test and be discarded. Repeat, ad nauseum and all rows fail the WHERE test and are discarded.

如果尝试使用WHERE MOD(ROWNUM,2)=1这样获得奇数行，则它将仅返回第一行，第二行以及随后的行将无法通过测试，并且永远不会包含在查询中.

If you try to get the odd rows this way using WHERE MOD(ROWNUM,2)=1 then it will return the first row only and the second, and subsequent, rows will fail the test and will never be included in the query.

正如Vijaykumar Hadalgi所建议的那样，您需要在子查询中选择ROWNUM(可以在其中对所有行进行编号，而没有where子句来限制它)，然后在外部查询中执行测试以限制行: /p>

As Vijaykumar Hadalgi suggests, you need to select the ROWNUM in a sub-query (where it can number all the rows without a where clause to restrict it) and then in the outer query perform the test to restrict the rows:

SELECT ename, job
FROM   (
  SELECT ename,
         job,
         ROWNUM AS row_id             -- Generate ROWNUM second.
  FROM   (
    SELECT ename, job
    FROM   Emp
    ORDER BY ename                    -- ORDER BY first.
  )
)
WHERE  MOD( row_id, 2 ) = 0;          -- Filter third.

SQLFIDDLE

这篇关于如何从oracle中的表中选择甚至记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！