mysql - 如何在MYSQL中按特定顺序条件从id中获取row_number？

Question

我正在寻找一个带有last_id和order条件的MYSQL分页解决方案，要使用LIMIT，OFFSET我需要找出last_id的偏移量。

例如，我有一个表news(id，title，updated_at)，第一页返回3,1,2,5,4，第二页我有3个参数(last_id是4，按updated_at desc排序，限制为5)，我需要通过updated_at desc计算出id 4 order的偏移量。

+------+-------+---------------------+
| id   | title | updated_at          |
+------+-------+---------------------+
|    3 | test3 | 2018-01-22 11:30:08 |
+------+-------+---------------------+
|    1 | test1 | 2018-01-21 12:30:08 |
+------+-------+---------------------+
|    2 | test2 | 2018-01-20 12:30:08 |
+------+-------+---------------------+
|    5 | test5 | 2018-01-19 13:30:08 |
+------+-------+---------------------+
|    4 | test4 | 2018-01-18 14:30:08 |
+------+-------+---------------------+

....

+------+-------+---------------------+
|    11 | test11 | 2018-01-17 14:30:08 |
+------+-------+---------------------+
|    12 | test12 | 2018-01-16 15:30:08 |
+------+-------+---------------------+

我想返回第二页的是11,12,....，有什么有效的方法可以实现吗？

Answer 1

通过updated_at desc计算出id 4订单的偏移量:

SELECT @rownum:=@rownum + 1 AS offset, id, title, updated_at 
FROM news, (SELECT @rownum:=0) AS r 
ORDER BY updated_at DESC;

现在您可以获得特定行的偏移量。

然后你可以这样做:

SELECT * FROM 
(SELECT @rownum:=@rownum + 1 AS offset, id, title, updated_at 
 FROM news, (SELECT @rownum:=0) AS r ORDER BY updated_at DESC) AS t 
WHERE offset > 5;