如何通过主键选择一行,在“上方"选择一行,在另一列“下方"选择一行? [英] How to select row by primary key, one row 'above' and one row 'below' by other column?
问题描述
好的,所以我有一个带有'id'列和'score'列的'users'表. 'id'是主键,'score'可以是任何正整数,并且用户可以具有相同的分数.
Ok, so I've got a 'users' table with an 'id' column and a 'score' column. 'id' is the primary key, 'score' can be any positive integer, and users can have the same score.
我需要选择3行:
- 目标用户",即"id"一行
- 按得分"对表进行排序时,位于目标用户正上方的用户
- 按得分"对表进行排序时,位于目标用户正下方的用户
关于如何在SQL中执行此操作的任何建议?谢谢!
Any suggestions on how to do this in SQL? Thanks!
更新
对不起,我现在意识到我需要对如何处理具有相同分数的多行做出更多选择.我已决定将排除打分,因此我正在寻找目标用户中得分第二高和得分最低的用户.
Sorry all, I realize now that I need to make more choices about how to handle multiple rows with the same score. I've decided that tying scores will be excluded, so I'm looking for a user with the next highest score and next lowest score from the target user.
样本数据:
id score
1 0
2 5
3 9
4 5
5 5 *
6 5
7 8 *
8 3 *
因此,如果我的目标用户的ID为5,则我希望ID为7、5和8的行
So, if my target user has id = 5, I want rows with ids 7, 5, and 8
推荐答案
首先,查询该特定用户的分数:
First, query the score for that particular user:
select score
from users
where id = 42
说用户42的得分是6.您可以查询下一个用户,例如:
Say the score for user 42 is 6. You can then query the next user like:
select name
, score
from users
where score > 6
order by
score
limit 1
和以前的用户类似:
select name
, score
from users
where score < 6
order by
score desc
limit 1
这篇关于如何通过主键选择一行,在“上方"选择一行,在另一列“下方"选择一行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!