SQL Server 选择最后 N 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4193705/
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 LAST N Rows
提问by Diego
This is a known question but the best solution I've found is something like:
这是一个已知问题，但我发现的最佳解决方案如下：
SELECT TOP N *
FROM MyTable
ORDER BY Id DESC
I've a table with lots of rows. It is not a posibility to use that query because it takes lot of time. So how can I do to select last N rows without using ORDER BY?
我有一张有很多行的桌子。使用该查询是不可能的，因为它需要很多时间。那么如何在不使用 ORDER BY 的情况下选择最后 N 行呢？
EDIT
编辑
Sorry duplicated question of this one
抱歉重复了这个问题
采纳答案by JonVD
You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:
您也可以使用 ROW NUMBER BY PARTITION Feature 来实现。一个很好的例子可以在这里找到：
I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:
SELECT ORDERID, CUSTOMERID, OrderDate FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,* FROM Orders ) as ordlist WHERE ordlist.EmployeeID = 5 AND ordlist.OrderedDate <= 5
我正在使用 Northwind 数据库的 Orders 表......现在让我们检索员工 5 下的最后 5 个订单：
SELECT ORDERID, CUSTOMERID, OrderDate FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,* FROM Orders ) as ordlist WHERE ordlist.EmployeeID = 5 AND ordlist.OrderedDate <= 5
回答by Niru Mukund Shah
You can make SQL server to select last N rows using this SQL:
您可以使用此 SQL 使 SQL 服务器选择最后 N 行：
select * from tbl_name order by id desc limit N;
回答by ABI
I tested JonVD's code, but found it was very slow, 6s.
我测试了 JonVD 的代码，但发现它很慢，6s。
This code took 0s.
这段代码花了 0 秒。
SELECT TOP(5) ORDERID, CUSTOMERID, OrderDate
FROM Orders where EmployeeID=5
Order By OrderDate DESC
回答by Prafulla Sutradhar
If you want to select last numbers of rows from a table.
如果要从表中选择最后几行。
Syntax will be like
语法会像
select * from table_name except select top
(numbers of rows - how many rows you want)* from table_name
These statements work but differrent ways. thank you guys.
这些语句的工作方式不同。谢谢你们。
select * from Products except select top (77-10) * from Products
in this way you can get last 10 rows but order will show descnding way
通过这种方式，您可以获得最后 10 行，但订单将显示降序方式
select top 10 * from products
order by productId desc
select * from products
where productid in (select top 10 productID from products)
order by productID desc
select * from products where productID not in
(select top((select COUNT(*) from products ) -10 )productID from products)
回答by Hakan F?st?k
In a very general way and to support SQL server here is
以非常通用的方式并在这里支持 SQL 服务器是
SELECT TOP(N) *
FROM tbl_name
ORDER BY tbl_id DESC
and for the performance, it is not bad (less than one second for more than 10,000 records On Server machine)
和性能，还不错（服务器机器上10,000多条记录不到一秒）
回答by Ardalan Shahgholi
First you most get record count from
首先，您最多可以从中获得记录数
Declare @TableRowsCount Int
select @TableRowsCount= COUNT(*) from <Your_Table>
And then :
进而 ：
In SQL Server 2012
在 SQL Server 2012 中
SELECT *
FROM <Your_Table> As L
ORDER BY L.<your Field>
OFFSET <@TableRowsCount-@N> ROWS
FETCH NEXT @N ROWS ONLY;
In SQL Server 2008
在 SQL Server 2008 中
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS sequencenumber, *
FROM <Your_Table>
Order By <your Field>
) AS TempTable
WHERE sequencenumber > @TableRowsCount-@N
回答by AdaTheDev
Is "Id" indexed? If not, that's an important thing to do (I suspect it is already indexed).
“Id”是否已编入索引？如果没有，这是一件很重要的事情（我怀疑它已经被编入索引）。
Also, do you need to return ALL columns? You may be able to get a substantial improvement in speed if you only actually need a smaller subset of columns which can be FULLY catered for by the index on the ID column - e.g. if you have a NONCLUSTERED index on the Id column, with no other fields included in the index, then it would have to do a lookup on the clustered index to actually get the rest of the columns to return and that could be making up a lot of the cost of the query. If it's a CLUSTERED index, or a NONCLUSTERED index that includes all the other fields you want to return in the query, then you should be fine.
另外，您是否需要返回所有列？如果您实际上只需要一个较小的列子集，而这些列子集可以完全由 ID 列上的索引提供——例如，如果您在 Id 列上有一个 NONCLUSTERED 索引，而没有其他的包含在索引中的字段，那么它必须对聚集索引进行查找才能真正返回其余的列，这可能构成查询的大量成本。如果它是一个 CLUSTERED 索引，或者一个包含您想在查询中返回的所有其他字段的 NONCLUSTERED 索引，那么您应该没问题。
回答by fth
select * from (select top 6 * from vwTable order by Hours desc) T order by Hours
回答by Dzamo Norton
Here's something you can try without an order bybut I think it requires that each row is unique. Nis the number of rows you want, Lis the number of rows in the table.
这里有一些你可以尝试的东西，order by但我认为它要求每一行都是独一无二的。 N是你想要的行数，L是表中的行数。
select * from tbl_name except select top L-N * from tbl_name
As noted before, which rows are returned is undefined.
如前所述，返回哪些行是未定义的。
EDIT: this is actually dog slow. Of no value really.
编辑：这实际上是狗慢。真没价值。
回答by timberhill
This query returns last N rows in correct order, but it's performance is poor
此查询以正确的顺序返回最后 N 行，但性能很差
select *
from (
select top N *
from TableName t
order by t.[Id] desc
) as temp
order by temp.[Id]
