mysql 选择前几行,MySQL：选择特定行之前和之后的5行

I have table called "users" and need to select 2 rows before and after specific row, sorted by users.score ASC

users table (structure):

id name score

1 John 2

2 Sara 1

3 san 3

4 test 2

5 jery 5

6 simon 6

7 bob2 7

8 jack 4

9 man 2

for example: need to select 2 rows before and after users.id = 5 order by users.score

result should be like:

id name score

3 san 3

8 jack 4

5 jery 5

6 simon 6

7 bob2 7

thanks,

解决方案

Using union all and subqueries to limit the records should do it:

select * from users where id = 5

union all (

select * from users

where score < (select score from users where id = 5)

order by score desc limit 2

)

union all (

select * from users

where score > (select score from users where id = 5)

order by score asc limit 2

)

order by score

Edit: I think a better method is to number the rows according to score and then select the rows with number -2 and +2 from the rows of id 5:

select id, name, score

from (select

t.*, @rownum1 := @rownum1 + 1 as rank

from users t, (select @rownum1 := 0) r

order by score

) a,

(select rank from (

select t.*,

@rownum := @rownum + 1 as rank

from users t, (select @rownum := 0) r

order by score

) t

where id = 5

) b

where b.rank between a.rank -2 and a.rank+2

order by score;