SQL 如何从oracle中的表中选择偶数记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20014495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to select even records from a table in oracle?
提问by Sai
i'm using below query to retrieve even numbered records.but in reslut it is displaying no rows
我正在使用下面的查询来检索偶数记录。但在 reslut 中它不显示任何行
select ename,job from emp where mod(rownum,2)=0;
why mod(rownum,2) is not working in where conditiom
为什么 mod(rownum,2) 在条件下不起作用
can u please also give the query to select the odd number records
你能否也给出查询来选择奇数记录
Is there anything wrong with that query?
该查询有什么问题吗？
suggetions please..
请提出建议..
回答by MT0
It is not working because: for the first row ROWNUMis 1and, in this case, MOD(ROWNUM,2)is 1and since your WHEREstatement is MOD(ROWNUM,2)=0then this reduces to 1=0and the row is discarded. The subsequent row will then be tested against a ROWNUMof 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 WHEREtest and are discarded.
它不工作，因为：第一行ROWNUM是1，在这种情况下，MOD(ROWNUM,2)是1和因为你的WHERE说法是MOD(ROWNUM,2)=0那么这减少了1=0与该行被丢弃。然后将针对 a ROWNUMof 1测试后续行（因为前一行不再在输出中并且没有行号），这将再次失败测试并被丢弃。重复，令人恶心，所有行都未通过WHERE测试并被丢弃。
If you try to get the odd rows this way using WHERE MOD(ROWNUM,2)=1then 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.
如果您尝试使用这种方式获取奇数行，WHERE MOD(ROWNUM,2)=1那么它将仅返回第一行和第二行，随后的行将无法通过测试并且永远不会包含在查询中。
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:
正如 Vijaykumar Hadalgi 所建议的，您需要在子查询中选择 ROWNUM（它可以在没有 where 子句的情况下对所有行进行编号以对其进行限制），然后在外部查询中执行测试以限制行：
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.
回答by AkhilKrishnan
Try this
尝试这个
To find the EVENnumber of row details you can use this code. This one is comfortable in oracle SQL as well as MySQL.
要查找偶数行详细信息，您可以使用此代码。 这个在 oracle SQL 和 MySQL 中都很舒服。
select * from (select ename,job, ROWNUM AS rn from emp) where mod(rn, 2) = 0;
To find the ODDnumber of row details you can use this code.
要查找行详细信息的奇数，您可以使用此代码。
select * from (select ename,job, ROWNUM AS rn from emp) where mod(rn, 2) <> 0;
回答by TUSHAR DHAMANKAR
-- for selecting rows 1,3,5,7....
SELECT EMPLOYEE_ID,FIRST_NAME, LAST_NAME FROM
(
SELECT DENSE_RANK OVER(ORDER BY EMPLOYEE_ID)AS RANK, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
)
WHERE MOD(RANK,2)=1
-- for selecting rows 2,4,6,8....
SELECT EMPLOYEE_ID,FIRST_NAME, LAST_NAME FROM
(
SELECT DENSE_RANK OVER(ORDER BY EMPLOYEE_ID)AS RANK, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
)
WHERE MOD(RANK,2)=0
回答by Md Wasi
select * from emp where empid in (select decode(mod(rownum,2),0, empid,null) from emp); --Alternate record Even numbered
Fetch even records from the table
从表中获取偶数记录
回答by iAsghar Hussain
Or You can try this:
或者你可以试试这个：
Here The inner query will fetch all the even records primary key value and based on that we can get all those record details.
这里内部查询将获取所有偶数记录的主键值，并基于此我们可以获得所有这些记录的详细信息。
SELECT * FROM EMP WHERE EMPNO IN
(
SELECT CASE MOD(ROWNUM,2)
WHEN 0 THEN EMPNO
END
FROM EMP
);
This will fetch all the odd records
这将获取所有奇数记录
SELECT * FROM EMP WHERE EMPNO IN
(
SELECT CASE MOD(ROWNUM,2)
WHEN 1 THEN EMPNO
END
FROM EMP
);
回答by nikhil sugandh
this will do :
这会做：
Select * from (Select rownum as No_of_Row, E.* from emp E)
Where mod(No_of_Row,2)=0;
回答by Amit Kumar
Use the below query u will get the result:
使用下面的查询你会得到结果：
SELECT empno,ename,slno
FROM ( SELECT empno,ename,ROW_NUMBER() OVER
(ORDER BY empno desc) AS slno FROM emp ) result WHERE mod(slno,2) = 1;
回答by PRASHANTKUMAR ILAGER
Use the below query,
使用以下查询，
select * from (select ename,job, ROWNUM as row_id from emp) where mod(row_id, 2) = 0;
回答by Tina sharma
Select ename, job from emp where mod(id, 2) =0 ;
You can Try the idcolumn intead of rownumif table has an identity column
你可以尝试id的列这一翻译rownum，如果表中有标识列
回答by vhadalgi
Please try :
请尝试 ：
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
Output:- 2 4 6
输出：- 2 4 6
Select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
Output: 1,3,5..
输出：1,3,5..
Or this will work
或者这会起作用
this :
这个 ：
odd :
奇怪的 ：
select * from
(
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1 DESC) AS 'RowNumber',
FROM table1
) d
where (RowNumber % 2) = 1
even :
甚至 ：
select * from
(
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1 DESC) AS 'RowNumber',
FROM table1
) d
where (RowNumber % 2) = 0
