我有一个非常简单的
MySQL表,我用admin_no为每个学生保存考试和CA分数的科目.它看起来像这样:
admin_no subject ca exam year class_s
10/00182 IRS 39 56 2014/2015 Grade 2
10/00177 English 39 59 2014/2015 Grade 2
10/00177 Mathematics 34 59 2014/2015 Grade 2
10/00177 Basic 37 59 2014/2015 Grade 2
10/00177 Social 39 60 2014/2015 Grade 2
10/00177 Yoruba 33 59 2014/2015 Grade 2
09/00159 English 37 59 2014/2015 Grade 2
09/00159 Mathematics 35 60 2014/2015 Grade 2
09/00159 Basic 39 59 2014/2015 Grade 2
.......................................................
我用下面的mysql查询求和所有科目的考试分数和CA,然后将每个学生的所有分数总计为total_scores,然后对总分进行排名:
SELECT admin_no,rank,total_score
FROM (SELECT *, IF(@marks=(@marks:=total_score), @auto, @auto:=@auto+1) AS rank
FROM (SELECT * FROM
(SELECT admin_no, SUM(exam)+SUM(ca) AS total