sql select * 在确切的行数之间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3635754/
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 select * between exact number of rows
提问by user414977
I would like to know if I could using select statement retrieve exact position of the rows. e.g rows between 235 & 250. Is this possible?
我想知道是否可以使用 select 语句检索行的确切位置。例如 235 和 250 之间的行。这可能吗？
Thanks in advance, shashi
提前致谢， shashi
采纳答案by Nitika Chopra
We can do this by multiple way.
我们可以通过多种方式做到这一点。
we can do with the help of offset-fetch clause.
select * from Table_Name order by Column_Name offset 234 rows fetch next 16 rows only
我们可以借助 offset-fetch 子句来完成。
select * from Table_Name order by Column_Name offset 234 rows fetch next 16 rows only
it will fetch the record between 235-250. because it will skip first 234 rows and will fetch next 16 rows.
它将获取 235-250 之间的记录。因为它将跳过前 234 行并获取接下来的 16 行。
we can use simple select statement with where clause.
Select * from Table_Name where Column_Name Between 235 and 250
我们可以使用带有 where 子句的简单 select 语句。
Select * from Table_Name where Column_Name Between 235 and 250
it will also fetch same result.
它也将获得相同的结果。
Hope it will help.
希望它会有所帮助。
回答by bwawok
I don't know of a general way.. but each DB has a way. For example in oracle you can do it with a nested select
我不知道一般的方法......但每个数据库都有一种方法。例如在 oracle 中，您可以使用嵌套选择来完成
Oracle:
甲骨文：
select * from (
select a, b, c from table_foo
where id = 124
)
WHERE rownum >= 235
and ROWNUM <= 250
MSSQL
MSSQL
select * from
(select Row_Number() over
(order by userID) as RowIndex, * from users) as Sub
Where Sub.RowIndex >= 235 and Sub.RowIndex <= 250
MySQL
MySQL
SELECT * FROM TableName LIMIT 235, 15
回答by Malachi
If your using mySQL you could use the limit command for example:
如果您使用 mySQL，您可以使用 limit 命令，例如：
SELECT * FROM TableName LIMIT 235, 15
Where the first number is the start index and the second is the number of rows to return.
其中第一个数字是起始索引，第二个数字是要返回的行数。
回答by Vivek Srivastava
This can be done very easily in SQL Server 2012. By using the new feature of OFFSETand FETCH. This will help you to pull out desired rows in an already ordered/sorted result set.
这可以在SQL Server 2012中很容易做到使用的新功能OFFSET和FETCH。这将帮助您在已排序/排序的结果集中提取所需的行。
Please see the below example:
请看下面的例子：
SELECT
PP.FirstName + ' ' + PP.LastName AS 'Name'
,PA.City
,PA.PostalCode
FROM Person.Address PA
INNER JOIN
Person.BusinessEntityAddress PBEA
ON PA.AddressID = PBEA.AddressID
INNER JOIN
Person.Person PP
ON PBEA.BusinessEntityID = PP.BusinessEntityID
ORDER BY PP.FirstName
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY
Please notice the OFFSET 0 and FETCH NEXT 5written above.
This is will display only 5 rows starting from 0 the row.
请注意OFFSET 0 and FETCH NEXT 5上面写的。
这将仅显示从 0 行开始的 5 行。
回答by Damian Leszczyński - Vash
No, that database is set not a sequence, this mean that You the don't have any specific order.
不，那个数据库不是一个序列，这意味着你没有任何特定的顺序。
But when specify the order than everything is much simpler.
但是当指定顺序时比一切都简单得多。
Oracle
甲骨文
SELECT * FROM ( SELECT * FROM TABLE ORDER BY COLUMN ) WHERE rownum BETWEEN 235 and 250
In this case You have to use rownum
在这种情况下，您必须使用 rownum
rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.
rownum 是一个伪列。它对结果集中的记录进行编号。满足 select 语句中 where 条件的第一条记录被赋予 rownum=1，并且满足相同条件的每个后续记录增加 rownum。
MS SQL
微软SQL
WITH OrderedRecords AS
(
SELECT ColumnA,
ROW_NUMBER() OVER (ORDER BY ColumnA) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT * FROM OrderedRecords WHERE RowNumber BETWEEN 235 and 250
GO
For this You have to specify You own order column
为此，您必须指定您自己的订单列
For MySQL i don't know how the engine deal with this.
对于 MySQL，我不知道引擎如何处理这个问题。
回答by Nitika Chopra
In SQL Server,
在 SQL Server 中，
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 行。
回答by Dileepa
Following worked from me in oracle
以下是我在 oracle 中的工作
select * from (select rownum serial,sp.* from sample_table st) sam
where sam.serial > 10 and sam.serial <= 20;
回答by Christopher Klein
If you're using Microsoft SQL (2005>) you can use the ROW_NUMBER function
如果您使用的是 Microsoft SQL (2005>)，则可以使用 ROW_NUMBER 函数
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
