按顺序获取数据库中的最后N行？ [英] Get the last N rows in the database in order?

问题描述

假设我有以下数据库表：

Let's say I have the following database table:

 record_id | record_date | record_value
-----------+-------------+--------------
         1 | 2010-05-01  |       195.00
         2 | 2010-07-01  |       185.00
         3 | 2010-09-01  |       175.00
         4 | 2010-05-01  |       189.00
         5 | 2010-06-01  |       185.00
         6 | 2010-07-01  |       180.00
         7 | 2010-08-01  |       175.00
         8 | 2010-09-01  |       170.00
         9 | 2010-10-01  |       165.00

我想用record_date ASC排序的数据来获取最后5行。这很容易做到：

I want to grab the last 5 rows with the data ordered by record_date ASC. This is easy to do with:

SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5 OFFSET 4

哪个会给我：

 record_id | record_date | record_value
-----------+-------------+--------------
         6 | 2010-07-01  |       180.00
         7 | 2010-08-01  |       175.00
         3 | 2010-09-01  |       175.00
         8 | 2010-09-01  |       170.00
         9 | 2010-10-01  |       165.00

但是当我不知道有多少记录并且可以做到时，该怎么办？计算出4的幻数？

But how do I do this when I don't know how many records there are and can't compute the magic number of 4?

我已经尝试过该查询，但是如果记录少于5条，则会导致负的OFFSET，这是无效的：

I've tried this query, but if there are less than 5 records, it results in a negative OFFSET, which is invalid:

SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5 
    OFFSET (SELECT COUNT(*) FROM mytable) - 5;

那我该怎么做呢？

推荐答案

为什么不按相反的顺序订购？

Why don't you just order the opposite way?

SELECT * FROM mytable ORDER BY record_date DESC LIMIT 5;

如果您不想在应用程序中正确翻转，可以嵌套查询并翻转他们两次：

If you don't want to flip back correctly in the application, you can nest a query and flip them twice:

SELECT *
    FROM (SELECT * FROM mytable ORDER BY record_date DESC LIMIT 5)
    ORDER BY record_date ASC;

...事实证明这是相当便宜的操作。

...which turns out to be a pretty cheap operation.

这篇关于按顺序获取数据库中的最后N行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！