如何在mysql中的特定ID后获取记录 [英] How can get records after specific id in mysql

问题描述

我们在mysql上有这个

We have this on mysql:

并希望返回2个限制:
1- "SELECT * FROM table ORDER BY point DESC LIMIT 0,2"//返回'google'& 'jsfiddle'
2- "SELECT * FROM table ORDER BY point DESC LIMIT 2,2"//返回'stackoverflow'& 'msn'
3- "INSERT INTO table SET id = 11, name = 'gmail', point = 101 "
4- "SELECT * FROM table ORDER BY point DESC LIMIT 4,2"//返回'msn'& 'emra'

And want return with 2 limit:
1- "SELECT * FROM table ORDER BY point DESC LIMIT 0,2" //return 'google' & 'jsfiddle'
2- "SELECT * FROM table ORDER BY point DESC LIMIT 2,2" //return 'stackoverflow' & 'msn'
3- "INSERT INTO table SET id = 11, name = 'gmail', point = 101 "
4- "SELECT * FROM table ORDER BY point DESC LIMIT 4,2" //return 'msn' & 'emra'

第4个"msn"重复.我想4号返回'emra'& "facebook".

我可以保存"id"，并希望查询返回该ID之后的记录.

I can save 'id' and I want query that return records after that id.

让我用另一种方式询问:

我们有以下记录:

id, name, point
4, 'google', 100
6, 'yahoo', 100
3, 'gmail', 100
8, 'ymail', 100
2, 'fb', 100

我有id:3，并希望选择此id之后的记录(在此示例中为"ymail"，"fb")

I have id:3 and want select records that are after this id(in this example 'ymail', 'fb')

这是一个通过ajax加载内容(例如facebook)，但每时每刻都在变化的用户最大点数.

this is a ajax load content by scorlling(like facebook) but about max points of users that every moment is changing.

推荐答案

如果我正确理解了您的问题(对此表示怀疑)，则需要一种获取与您的搜索条件相匹配的其他热门结果的方法，在获得第一个N之后...

If i understand your question correctly (which i doubt), You want a way of fetching additional top results matching your search criteria, after getting first N...

主要问题是新记录(您在#3中显示)可能在以前的结果之间任意位置，从而使您已经获取的记录(按顺序)已过时.

The major problem with this is that the new record (which you show in #3) may go anywhere between the previous results, thus making the records you already fetched obsolete (in order).

因此，如果您真的想要这个，则应该做两件事:

So, if you really want this, you should do two things:

1. 记住所有您已经获取的ID，并在查询中在WHERE子句中提供NOT IN(list of already fetched ids)，而不指定偏移量(仅LIMIT 2)

1. Remember all the IDs you already fetched, and in query provide NOT IN(list of already fetched ids) in WHERE clause, without specifying offset (only LIMIT 2)

获取后，将重新获取的结果量添加到列表中，并在需要时对其进行重新排序(某种插入排序)

After you fetched that, add the amount of the results you fetched anew to the list and reorder it if needed (some sort of insertion sort)

这篇关于如何在mysql中的特定ID后获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！