我正在使用下面的查询来检索偶数记录.但在结果中它不显示任何行
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
这篇关于如何从oracle的表中选择偶数记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!