如何从oracle的表中选择偶数记录?

2021-12-06 00:00:00 sql 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

那个查询有什么问题吗?

Is there anything wrong with that query?

请提出建议..

推荐答案

它不起作用，因为:对于第一行 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 子句的情况下对所有行进行编号以对其进行限制)，然后在外部查询中执行测试以限制行:

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

相关文章