从 MySQL 中选择最后 N 行

2021-11-20 00:00:00 database mysql

我想从 MySQL 数据库中选择名为 id 的列中的最后 50 行，该列是主键.目标是行应该按照 ASC 顺序按 id 排序，这就是此查询不起作用的原因

I want to select last 50 rows from MySQL database within column named id which is primary key. Goal is that the rows should be sorted by id in ASC order, that’s why this query isn’t working 

SELECT 
    *
FROM
    `table`
ORDER BY id DESC
LIMIT 50;

另外值得注意的是，可以操作(删除)行，这就是为什么以下查询也不起作用

Also it’s remarkable that rows could be manipulated (deleted) and that’s why following query isn’t working either

SELECT 
    *
FROM
    `table`
WHERE
    id > ((SELECT 
            MAX(id)
        FROM
            chat) - 50)
ORDER BY id ASC;

问题:如何从 MySQL 数据库中检索可操作且按 ASC 顺序排列的最后 N 行?

Question: How is it possible to retrieve last N rows from MySQL database that can be manipulated and be in ASC order ?

推荐答案

您可以使用子查询来实现:

You can do it with a sub-query:

SELECT * FROM (
    SELECT * FROM table ORDER BY id DESC LIMIT 50
) sub
ORDER BY id ASC

这将从table中选择最后 50行，然后按升序排列.

This will select the last 50 rows from table, and then order them in ascending order.

相关文章