部分获得有序行

Question

Hello developers out there,

I want to create a ranking list in my program, and for that want to get the users of the mysql database ordered by their points. I'm doing this with:

select `name`,`points` from users order by `points` desc

For better performance i don't want to get all users but just 10+ and 10- from a given user, because i don't want to show the whole ranking list in the program anyways. Could somebody help me with this?

Answer 1

One method uses subqueries and union all:

(select name, points
 from users
 where points <= (select u2.points from users u2 where u2.name = ?)
 order by points desc
 limit 11
) union all
(select name, points
 from users
 where points > (select u2.points from users u2 where u2.name = ?)
 order by points asc
 limit 10
) ;

This returns 21 rows, with the specified user included.