MySql 查询选择最高的学生分数，包括必须学科分数

Question

我在这里试图归档的是，仅选择学生表现最好的科目，包括必修科目 (mustSubjectId)，并根据成绩范围总计输出结果。
等级越高，分数越低添加总和(标记)作为分数。

输入参数示例:

$subjectlimit = '3'; // total subjects a student should pass including mandatory subject to qualify 
$idexam = '1'; // targeted examination id 
$mustSubjectId = '201'; // mandatory subjectid
$studentId = '1'; // a student to get

表格检查

idcandidate | subject_id   | score------------------------------------1           |    200       | 30 1           |    201       | 60 (note: a must subject)1           |    202       | 80 1           |    204       | 90 1           |    203       | 50 1           |    205       | 54 1           |    209       | 37 8           |    200       | 68 2           |    200       | 20 1           |    206       | 50 

考试规则表

idexam          | marks       | grade---------------------------------------1               |    30       | 9 1               |    45       | 8 1               |    65       | 7 1               |    70       | 61               |    80       | 5 1               |    85       | 4 1               |    90       | 3 1               |    95       | 2 1               |    100      | 1 

输出限制为3

Idcandidate| idexam   | subject_id| grade1          |     1    | 204       |    31          |     1    | 202       |    51          |     1    | 201       |    8

最终输出

Idcandidate| idexam  | points1          |     1   |    16

我不擅长复杂的 MySql 查询，希望你能帮忙

编辑

I was mistaken by JNevill in the comments, the table examinations does not have idexam and no relationship with examrules. The selection should be something like

Select top scores including mandatory subject score from examinations where idcandidate = ‘1’ limit 3 then check the selected scores grade in examrules where idexam = ‘1’ , the grades which will be found should be calculated as points sum (grade) as points

查询的目的

There is a sheet showing the grades of all subjects of a class against students, then on a certain row the sheet suppose to show the points the student gets on 3 subjects including mandatory subject. For a student to pass examination he/she should pass three subjects including mandatory subject.

注意正在运行的sql报告无法识别select top，我也尝试使用fiddle，fiddle也无法识别它。 从 idcandidate = ‘1’的考试中选择前 3 名

Answer 1

我建议类似(根据需要用参数替换值):

这将为您提供前 2 个最高分，但这些分数不是您的“必修科目”:

select TOP 2 en.idcandidate, en.idexam, en.subject_id, en.grade
FROM examinations en
INNER JOIN examrules er ON en.idexam = er.idexam
where en.subject <> $mustSubjectId
order by en.score desc

这为您提供了“必须主题”:

select en.idcandidate, en.idexam, en.subject_id, en.grade
FROM examinations en
INNER JOIN examrules er ON en.idexam = er.idexam
where en.subject = $mustSubjectId

这将为您提供“限制为 3 的输出”(只需将一个附加到另一个):

select TOP 2 en.idcandidate, en.idexam, en.subject_id, en.grade
FROM examinations en
INNER JOIN examrules er ON en.idexam = er.idexam
where en.subject <> $mustSubjectId
order by en.score desc
UNION ALL
select en.idcandidate, en.idexam, en.subject_id, en.grade
FROM examinations en
INNER JOIN examrules er ON en.idexam = er.idexam
where en.subject = $mustSubjectId

最后将成绩相加:

select idcandidate, idexam, sum(en.grade) as points FROM
(
    select TOP 2 en.idcandidate, en.idexam, en.subject_id, en.grade
    FROM examinations en
    INNER JOIN examrules er ON en.idexam = er.idexam
    where en.subject <> $mustSubjectId
    order by en.score desc
    UNION ALL
    select en.idcandidate, en.idexam, en.subject_id, en.grade
    FROM examinations en
    INNER JOIN examrules er ON en.idexam = er.idexam
    where en.subject = $mustSubjectId
) a group by a.idcandidate, a.idexam

我还没有机会对此进行测试，但希望有所帮助!