首页 文章

mysql查询计算学生成绩行以通过asc或desc获取订单[关闭]

提问于
浏览 次
-2

在mysql数据库表考试中，学生根据他/她写的科目出现，下面的查询输出的是为EXAM标记的学生评估的评估_id = 1

SELECT DISTINCT exam.fname, exam.lname, exam.idcandidate
FROM examinations
AS exam
INNER JOIN examinfo 
AS info
ON exam.subject_id = info.subjectid
WHERE info.assessment_id = '1'

现在我想根据学生对他/她的科目DESC或ASC的总分进行排序

存储学生分数的字段在考试表中称为“ score ”，请帮助我如何归档这个或者正确的方法 .

mysql sql

1 回答

  • 2

    计算每个exam.fname和每个exam.lname以及每个exam.idcandidate的总计

    SELECT exam.fname, exam.lname, exam.idcandidate, sum(exam.score) total
from examinations exam
     inner join examinfo info   
on exam.subject_id = info.subjectid
where info.assessment_id = '1'
group by exam.fname, exam.lname, exam.idcandidate
order by total desc;

    计算每个exam.idcandidate的总数

    SELECT distinct exam.fname, exam.lname, exam.idcandidate
from examinations exam
     inner join examinfo info 
     on exam.subject_id = info.subjectid
     inner join (select idcandidate, sum(score) total from examinations group by idcandidate) tot 
     on exam.idcandidate = tot.idcandidate
where info.assessment_id = '1'
order by tot.idcandidate desc;

    计算每个exam.idcandidate的总数（对于assessment_id ='1'）

    SELECT distinct exam.fname, exam.lname, exam.idcandidate
from examinations exam
     inner join examinfo info 
     on exam.subject_id = info.subjectid
     inner join (select ex.idcandidate, sum(ex.score) total 
                  from examinations ex inner join examinfo ei 
                    on ex.subject_id = ei.subjectid 
                 where ei.assessment_id = '1' group by idcandidate) tot 
     on exam.idcandidate = tot.idcandidate
where info.assessment_id = '1'
order by tot.idcandidate desc;
    回复于

相关问题