我的问题是，
我在mysql 中有一张表
哪些色谱柱是
id Student score
1 A 55
2 B 86
3 C 65
4 D 23
5 E 84
6 F 45
7 G 80
我想根据分数找出全班学生的排名，得分稍高的学生是他，得分稍低的另一个学生是他。例如，如果我正在搜索学生E
那么输出应该是
id User score rank_in_classs
2 B 86 1
5 E 84 2
7 G 80 3
另一个例子可以是，如果我正在寻找学生
id User score rank_in_classs
3 c 65 4
1 A 55 5
6 F 45 6
如何使用mysql查询找到它。
感谢
查询
SELECT id, Student, score,
FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM tbl )
) AS rank_in_class
FROM tbl
ORDER BY rank_in_class
LIMIT 3;
演示
使用Ullas提出的基于FIND_IN_SET（）的解决方案，您可以通过以下方式定位特定的学生+/-1排名：
set @this_rank := (
SELECT
FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM tbl
)
) AS rank_in_class
FROM tbl
where student = 'A'
);
select
*
from (
SELECT
id
, Student
, score
, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM tbl
)
) AS rank_in_class
FROM tbl
) ric
where rank_in_class between @this_rank-1 AND @this_rank+1
ORDER BY rank_in_class
LIMIT 3;
注意：如果您将秩值存储在表中，那么这就不会那么麻烦，而且可以执行得更好。
最好的方法可能是混合使用两个查询和一点PHP。
- 第一个查询检索您想要的"专注"学生的排名
- 第二个查询使用动态
LIMIT语句检索关注的"学生"周围的所有学生的范围（这是PHP不可避免的使用）
所以像这样的东西可能会起作用：
$user = 'C'; // Student user we want to "focus"
$range = 1; // Range around the "focus" : 1 before and 1 after (could be changed to anything else)
// First query : retrieving the rank of the "focused" student
$stmt = $mysqli->prepare('SELECT COUNT(*) AS Rank FROM Student AS Focused INNER JOIN Student as Others ON Others.Score > Focused.Score OR (Others.Score = Focused.Score AND Others.Id > Focused.Id) WHERE Focused.user = ?');
$stmt->bind_param('s',$user);
$stmt->execute();
$res = $stmt->get_result()->fetch_assoc();
$startRank = $res['Rank'];
// Computing the dynamic LIMIT
if (($startRank - $range) < 1) {
$offset = 0;
$rowCount = $startRank + $range + 1;
} else {
$offset = $startRank - $range;
$rowCount = ($range * 2)+1;
}
// Second query : retrieving the rank of all the students around the "focused" student
$stmt = $mysqli->prepare('SELECT id, user, score, @curRank := @curRank + 1 AS rank FROM Student, (SELECT @curRank := ?) Rank ORDER BY Score DESC, id DESC LIMIT ?, ?');
$stmt->bind_param('iii',$offset,$offset,$rowCount);
$stmt->execute();
这可能是查询数据库以获得所需内容的最佳方式。作为奖励，您可以将范围更改为您喜欢的任何范围。