MySql分页结果，找到“页面"对于特定结果 [英] MySql paginated results, find "page" for specific result

问题描述

我有一个使用MySql数据库的应用程序，该数据库显示了分页的记录列表，用户可以向其中添加新记录.我知道如何使用LIMIT等获得分页结果，但问题是在添加新记录时如何进入特定页面.如果页面大小为20，并且用户正在查看第一页，并且他们添加了第23条记录(表示100条记录)，那么我们如何确定向用户显示哪个页面.

I have an application using a MySql database which is displaying a paginated list of records, to which to which the user is able to add new ones. I know how to get paginated results using LIMIT etc, but the question is how to go a specific page when adding a new record. If the page size is 20, and the user is viewing the first page, and they add a record which is 23rd (out of say 100), how do we determine which page to show the user.

因此，从本质上讲，给定页面大小和特定记录，我如何确定要在页面中显示哪个页面".总页数不包括它们所属表的所有记录，但是获取它们的条件是静态的.

So, essentially, given a page size and a specific record, how do I determine which "page" to show out of the pages. The total pages do NOT include all of the records for the table they belong to, but the criteria for getting them is static.

我应该更具体一些.记录包含唯一的ID字段和字符串名称字段.结果记录按名称的字母顺序排序.另外，这是一个Java应用程序.

I should have been more specific. The records contain a unique ID field and a string name field. The resultant records are sorted by the name alphabetically. Also, this is a java application.

我唯一想到的是选择所有按名称排序的所需记录，然后选择如何在特定记录的结果中查找位置.由于我们知道页面大小，因此可以从该位置轻松计算页码，但是我不确定它们是否是MySql语法来获取结果在记录中的位置.

The only thing I can think of is to select all the desired records sorted by name and then some how find the position in those results of the specific record. From that position the page number could be easily calculated since we know the page size, but I'm not sure if they is MySql syntax for getting the position of a record in the results.

笨拙"的解决方案是只抓住它们，然后在应用程序代码(java)中确定所有结果中特定记录的位置.但是似乎他们必须是一种更有效的方法.

The "dumb" solution would be to just grab them all and then in the application code (java), determine the specific records position in all the results. But is seems like their must be a more efficient way.

推荐答案

SELECT COUNT(*) as CNT FROM tbl WHERE name < (SELECT name FROM tbl WHERE id = 42)

之后，只需将CNT值除以每页的行数并将其四舍五入(Math.floor())

After that you just divide CNT value to the amount rows per page and round (Math.floor()) it

或者您甚至可以在sql中做到这一点:

Or you can even do that in sql:

SELECT FLOOR(COUNT(*) / 20) AS page_number ...

这篇关于MySql分页结果，找到“页面"对于特定结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！