我有一个包含150行的SQL Server 2008表MEN。
我该如何只显示偶数或奇数行?
我有一个包含150行的SQL Server 2008表MEN。
我该如何只显示偶数或奇数行?
Check out ROW_NUMBER()
SELECT t.First, t.Last
FROM (
SELECT *, Row_Number() OVER(ORDER BY First, Last) AS RowNumber
--Row_Number() starts with 1
FROM Table1
) t
WHERE t.RowNumber % 2 = 0 --Even
--WHERE t.RowNumber % 2 = 1 --Odd
Select * from MEN where (RowID % 2) = 1
展示偶数的方法:Select * from MEN where (RowID % 2) = 0
更快: 使用位运算代替取模。
select * from MEN where (id&1)=0;
奇数查询:
SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.rn,2) = 1
偶数查询:
SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.rn,3) = 0
对于偶数值记录:
select * from www where mod(salary,2)=0;
对于奇数值的记录:
select * from www where mod(salary,2)!=0;
select * from(
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1 DESC) AS 'RowNumber',
FROM table1
) d where (RowNumber % 2) = 1
即使:
select * from(
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1 DESC) AS 'RowNumber',
FROM table1
) d where (RowNumber % 2) = 0
SELECT * FROM (SELECT ROW_NUMBER () OVER (ORDER BY sal DESC) row_number, sr,sal FROM empsal) a WHERE (row_number%2) = 1
and
SELECT * FROM (SELECT ROW_NUMBER () OVER (ORDER BY sal DESC) row_number, sr,sal FROM empsal) a WHERE (row_number%2) = 0
SELECT *
FROM
(
SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.rn,2) = 1
select * from Tablename
where id%2=0
USE TSQL2012;
GO
返回员工ID中的偶数编号:
SELECT *
FROM HR.Employees
WHERE (empid % 2) = 0;
GO
返回员工ID的奇数编号:
SELECT *
FROM HR.Employees
WHERE (empid % 2) = 1;
GO
希望这就是你在寻找的答案。