SQL 如何跳过sql查询中的前n行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29894645/
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 skip the first n rows in sql query
提问by Ambkrish
I want to fire a Query "SELECT * FROM TABLE" but select only from row N+1. Any idea on how to do this?
我想触发一个查询“ SELECT * FROM TABLE”，但只从行中选择N+1。关于如何做到这一点的任何想法？
采纳答案by Vikrant
Query: in 
sql-server
查询：在sql-server 中
DECLARE @N INT = 5 --Any random number
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RoNum
, ID --Add any fields needed here (or replace ID by *)
FROM TABLE_NAME
) AS tbl
WHERE @N < RoNum
ORDER BY tbl.ID
This will give rows of Table, where rownumber is starting from @N + 1.
这将给出表的行，其中 rownumber 从 开始@N + 1。
回答by Majid Basirati
Use this:
用这个：
SELECT *
FROM Sales.SalesOrderHeader
ORDER BY OrderDate
OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY
回答by dzhu
SQL Server:
SQL 服务器：
select * from table
except
select top N * from table
Oracle up to 11.2:
Oracle 到 11.2：
select * from table
minus
select * from table where rownum <= N
with TableWithNum as (
select t.*, rownum as Num
from Table t
)
select * from TableWithNum where Num > N
Oracle 12.1 and later (following standard ANSI SQL)
Oracle 12.1 及更高版本（遵循标准 ANSI SQL）
select *
from table
order by some_column
offset x rows
fetch first y rows only
They may meet your needs more or less.
它们可能或多或少地满足您的需求。
There is no direct way to do what you want by SQL. However, it is not a design flaw, in my opinion.
没有直接的方法可以通过 SQL 执行您想要的操作。但是，在我看来，这不是设计缺陷。
SQL is not supposed to be used like this.
SQL 不应该像这样使用。
In relational databases, a table represents a relation, which is a set by definition. A set contains unordered elements.
在关系数据库中，一个表代表一个关系，根据定义是一个集合。集合包含无序元素。
Also, don't rely on the physical order of the records. The row order is not guaranteed by the RDBMS.
另外，不要依赖记录的物理顺序。RDBMS 不保证行顺序。
If the ordering of the records is important, you'd better add a column such as `Num' to the table, and use the following query. This is more natural.
如果记录的顺序很重要，您最好在表中添加一个列，例如“Num”，并使用以下查询。这更自然。
select *
from Table
where Num > N
order by Num
回答by Felipe V. R.
In order to do this in SQL Server, you must order the query by a column, so you can specify the rows you want.
为了在 SQL Server 中执行此操作，您必须按列对查询进行排序，以便您可以指定所需的行。
Example:
例子：
select * from table order by [some_column]
offset 10 rows
FETCH NEXT 10 rows only
回答by Carlos Toledo
Do you want something like in LINQ skip 5 and take 10?
你想在 LINQ 中跳过 5 并取 10 吗？
SELECT TOP(10) * FROM MY_TABLE
WHERE ID not in (SELECT TOP(5) ID From My_TABLE);
This approach will work in any SQL version.
这种方法适用于任何 SQL 版本。
回答by Khurram Hassan
I know it's quite late now to answer the query. But I have a little different solution than the others which I believe has better performance because no comparisons are performed in the SQL query only sorting is done. You can see its considerable performance improvement basically when value of SKIP is LARGE enough.
我知道现在回答问题已经很晚了。但是我的解决方案与其他解决方案略有不同，我认为这些解决方案具有更好的性能，因为在 SQL 查询中不进行比较，只进行排序。当SKIP的值足够大时，您基本上可以看到其显着的性能提升。
Best performancebut only for SQL Server 2012 and above. Originally from @Majid Basirati's answerwhich is worth mentioning again.
DECLARE @Skip INT = 2, @Take INT = 2 SELECT * FROM TABLE_NAME ORDER BY ID ASC OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLYNot as Good as the first one but compatible with SQL Server 2005 and above.
DECLARE @Skip INT = 2, @Take INT = 2 SELECT * FROM ( SELECT TOP (@Take) * FROM ( SELECT TOP (@Take + @Skip) * FROM TABLE_NAME ORDER BY ID ASC ) T1 ORDER BY ID DESC ) T2 ORDER BY ID ASC
最佳性能，但仅适用于SQL Server 2012 及更高版本。最初来自@Majid Basirati 的回答，值得再次提及。
DECLARE @Skip INT = 2, @Take INT = 2 SELECT * FROM TABLE_NAME ORDER BY ID ASC OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY不如第一个，但与SQL Server 2005 及更高版本兼容。
DECLARE @Skip INT = 2, @Take INT = 2 SELECT * FROM ( SELECT TOP (@Take) * FROM ( SELECT TOP (@Take + @Skip) * FROM TABLE_NAME ORDER BY ID ASC ) T1 ORDER BY ID DESC ) T2 ORDER BY ID ASC
回答by Tobias
What about:
关于什么：
SELECT * FROM table LIMIT 50 OFFSET 1
回答by fspino
This works with all DBRM/SQL, it is standard ANSI:
这适用于所有 DBRM/SQL，它是标准的 ANSI：
SELECT *
FROM owner.tablename A
WHERE condition
AND n+1 <= (
SELECT COUNT(DISTINCT b.column_order)
FROM owner.tablename B
WHERE condition
AND b.column_order>a.column_order
)
ORDER BY a.column_order DESC
回答by Ragno Croft
In Faircom SQL (which is a pseudo MySQL), i can do this in a super simple SQL Statement, just as follows:
在 Faircom SQL（它是一个伪 MySQL）中，我可以在一个超级简单的 SQL 语句中做到这一点，如下所示：
SELECT SKIP 10 * FROM TABLE ORDER BY Id
Obviously you can just replace 10with any declared variable of your desire.
显然，您可以替换10为您想要的任何声明变量。
I don't have access to MS SQL or other platforms, but I'll be really surprised MS SQL doesn't support something like this.
我无法访问 MS SQL 或其他平台，但我真的很惊讶 MS SQL 不支持这样的东西。
回答by siddharth vara
try below query it's work
尝试下面的查询它的工作
SELECT * FROM `my_table` WHERE id != (SELECT id From my_table LIMIT 1)
Hope this will help
希望这会有所帮助
