mysql中的负极限偏移 [英] Negative limit offset in mysql

问题描述

我正在创建一个高分服务器，所需功能之一就是能够在用户当前分值附近检索高分.我目前有以下内容:

I'm creating a high score server and one of the needed features is being able to retrieve high scores around the users current score. I currently have the following:

SELECT * FROM highscores 
WHERE score >= ( SELECT score FROM highscores WHERE userID = someID ) 
ORDER BY score, updated ASC 
LIMIT -9, 19

这里唯一的问题是LIMIT的offset参数不能为负，否则我认为这会很不错.因此，总而言之，是否有任何技巧/方法可以为LIMIT偏移量提供负偏移量，或者也许有一种更好的方法可以完全解决此问题?

The only problem here is that the offset parameter of LIMIT can't be negative, otherwise I believe this would work dandy. So in conclusion, is there any trick / way to supply a negative offset to the LIMIT offset, or is there perhaps a better way to about this entirely?

推荐答案

您可以在对接单选择查询中做一个真正的痛苦，或者只是这样做:

You can either do a real pain in the butt single select query, or just do this:

(SELECT * FROM highscores 
WHERE score <= ( SELECT score FROM highscores WHERE userID = someID ) 
ORDER BY score, updated ASC 
LIMIT 9)
UNION
(SELECT * FROM highscores 
WHERE score = ( SELECT score FROM highscores WHERE userID = someID ))
UNION 
(SELECT * FROM highscores 
WHERE score >= ( SELECT score FROM highscores WHERE userID = someID ) 
ORDER BY score, updated ASC
LIMIT 9)

我拼凑一块以获取指示用户的分数，因此它位于列表的中间.如果需要，则为可选.另外，请勿使用SELECT *，而应使用特定字段.清晰度始终是可取的，在性能方面，*很烂.

I threw in a piece to grab the indicated user's score so it's in the middle of the list. Optional if you need it. Also, don't use SELECT *, use specific fields. Clarity is always preferable, and performance wise, * sucks.

这篇关于mysql中的负极限偏移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！