SQL Server 选择两个值之间的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5481002/
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
SQL Server select rows between two values
提问by neolaser
Hopefully the title explains it enough, but I want to be able to select rows in an SQL Server table between two values
希望标题足够解释它，但我希望能够在两个值之间选择 SQL Server 表中的行
example
例子
SELECT * FROM table WHERE rows between 20 and 60
SELECT * FROM table WHERE rows between 20 and 60
I have tried the ROW_NUMBER() and then use a WHERE clause....
我试过 ROW_NUMBER() 然后使用 WHERE 子句....
Thanks,
谢谢，
Jason
杰森
回答by Martin Booth
SELECT TOP 40 * FROM (SELECT TOP 60 * FROM table ORDER BY xx ASC) t ORDER BY xx DESC
Since the output of a select statement can return records in any order (without an order by clause) you need to decide which order to apply to the records... use the primary key if you don't know or care (substitute for xx)
由于 select 语句的输出可以以任何顺序返回记录（没有 order by 子句），您需要决定将哪个顺序应用于记录...如果您不知道或不关心，请使用主键（替换为 xx )
回答by Hogan
WITH mytable AS
(
SELECT *,
ROW_NUMBER() OVER (order by colname) AS 'RowNumber'
FROM table
)
SELECT *
FROM myTable
WHERE RowNumber BETWEEN 20 AND 60;
回答by MikeTeeVee
If you have SQL Server 2012 (or higher) you may use Offset-Fetch for this.
See this Microsoft Technet Article on the Offset-Fetch Clause.
You will need to specify an Order-By (which I think is obvious).
如果您有 SQL Server 2012（或更高版本），您可以为此使用 Offset-Fetch。
请参阅有关 Offset-Fetch Clause 的这篇Microsoft Technet 文章。
您需要指定一个 Order-By（我认为这很明显）。
If you want Rows Between 20 and 60, then what you're really saying is you want to start at 20 (your Offset) and then select (or Fetch) the next 40.
如果您想要 20 到 60 之间的行，那么您真正要说的是要从 20（您的偏移量）开始，然后选择（或获取）接下来的 40。
SELECT *
FROM TableName
ORDER BY SomeColumnName
OFFSET 20 ROWS
FETCH NEXT 40 ROWS ONLY
You can even use Variables and Calculations for your Fetch and Offset values.
Here's an example for exactly what the question asks for: Rows between 20 and 60
您甚至可以对 Fetch 和 Offset 值使用变量和计算。
这是问题要求的确切示例：20 到 60 之间的行
DECLARE @RowStart Int = 20
DECLARE @RowEnd Int = 60
SELECT *
FROM TableName
ORDER BY SomeColumnName
OFFSET @RowStart ROWS
FETCH NEXT (@RowEnd - @RowStart) ROWS ONLY
回答by pcofre
In previous versions of SQL, an option is to use a temporary table:
在以前版本的 SQL 中，一个选项是使用临时表：
SELECT IDENTITY(int,1,1) RowNumber,*
into #Temp
FROM Table1
SELECT *
FROM #Temp
WHERE RowNumber between 20 and 60
回答by Kuldeep Singh
Select * from (Select row_number() over(order by Column_name) as Num ,Col_name1,Col_name2,Col_name3 from table_name) Table_name where Num>5 and Num<10;
Select * from (Select row_number() over(order by Column_name) as Num ,Col_name1,Col_name2,Col_name3 from table_name) Table_name where Num>5 and Num<10;
For Example:
例如：
Select * from emp;
从 emp 中选择 *；
SQL> select * from emp;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
已选择 14 行。
SQL> Select * from (Select row_number() over(order by empno) as Num,ename,empno,deptno,sal from emp) emp where Num>5 and Num<10;
SQL> Select * from (Select row_number() over(order by empno) as Num,ename,empno,deptno,sal from emp) emp where Num>5 and Num<10;
NUM ENAME EMPNO DEPTNO SAL
6 BLAKE 7698 30 2850
7 CLARK 7782 10 2450
8 SCOTT 7788 20 3000
9 KING 7839 10 5000
SQL>
查询>
回答by Nitika Chopra
This query may help you:
此查询可能会帮助您：
select * from tablename order by columnname offset 20 rows fetch next 40 rows only
It treats 21st row as 1st row and fetches next 40 rows from a 21st row.
它将第 21 行视为第 1 行，并从第 21 行获取接下来的 40 行。
