感谢所有提出这个问题的人。
我已经找到了group_concat的解决方案。查询:
SELECT nisn,sum(nilai_angka)
( select find_in_set( sum(nilai_angka),
( select
group_concat(distinct sum(nilai_angka)
order by sum(nilai_angka) DESC separator ',')
from nilai))
) as rangking
FROM nilai
group by nisn
但是我遇到了问题,因为find_in_set和group_concat无法收到 SUM。
知道吗?
对不起,我真的可以理解数据库中字段的名称,但这是我上次工作面试中一项任务的截图,这与你的要求非常相似。
SELECT
CONCAT(pF," ",pI," ",pO) AS pupil,
#concat last,first and middle name of the student
@curRank := IF(@prevVal=score, @curRank, @curRank:= @curRank + 1 ) AS rank,
#compare scores to previous student to split places in case of equal results
@prevVal:=score as score
#save current score to compare with next student
FROM
(SELECT @curRank := 0, @prevVal:=null) r,
#trick to init variables without second query
tbl_results
#Fields: puppil_id, subject_id, score
LEFT JOIN tbl_pupils ON tbl_results.pupil_id=tbl_pupils.pidx
#Fields: pidx, pF, pI, pO
WHERE subject_id=2
ORDER BY score DESC;
这里的诀窍是SELECT @curRank:=
在语句ORDER BY
之后计算,这使得它变得非常容易。这是一个交互式版本: http://sqlfiddle.com/#!2/fee20/15但此刻似乎已经倒下了。