MySQL - 在后续比较中使用派生列名称

Question

我有以下两个表:

(很抱歉无法将它们正确粘贴到此处，因此创建了一个快照并给出了它的链接):

表考试

+---------+------------+-------------+  
| exam_id | exam_date  | description |  
+---------+------------+-------------+  
|       1 | 2016-06-01 | Exam 1      |  
|       2 | 2016-06-06 | Exam 2      |  
|       3 | 2016-06-07 | Exam 3      |  
|       4 | 2016-06-10 | Exam 4      |  
+---------+------------+-------------+  

表格主题

+------------+---------+-------------+  
| subject_id | exam_id | result      |  
+------------+---------+-------------+  
|          1 |       1 | Attended    |  
|          2 |       1 | Fail        |  
|          3 |       2 | Distinction |  
|          4 |       2 | Distinction |  
|          5 |       3 | Pass        |  
|          6 |       3 | Distinction |  
|          7 |       4 | Attended    |  
|          8 |       4 | Pass        |  
+------------+---------+-------------+  

“结果”字段中可能的值为:

不及格、参加、通过、优秀

目的是根据该考试的从属科目取得的成绩来获得考试的总体结果 - 该考试中某一科目“未通过”将意味着总体结果“未通过”。 “已参加”意味着该科目的成绩尚未确定，除非任何其他科目已经取得“不及格”成绩，否则该结果只能称为“已参加”。同样，只有在考试的所有科目中都取得“优异”成绩才能获得“优异”。

查询:

SELECT exams.exam_id, exam_date, description, 
@count_fail         :=  SUM(result='Fail')          AS count_fail, 
@count_attended     :=  SUM(result='Attended')      AS count_attended, 
@count_pass         :=  SUM(result='Pass')          AS count_pass, 
@count_distinction  :=  SUM(result='Distinction')   AS count_distinction,
CASE 
    WHEN (@count_fail >= @count_attended AND @count_fail >= 1)
        THEN 'Fail'
    WHEN (@count_attended >= @count_pass AND @count_attended >= 1)
        THEN 'Attended'
    WHEN (@count_pass >= @count_distinction AND @count_pass>= 1)    
        THEN 'Pass'
    ELSE 
        'Distinction'
    END AS final_result FROM exams 
INNER JOIN subjects ON exams.exam_id = subjects.exam_id GROUP BY exam_id

结果让我难住了......

+---------+------------+-------------+------------+----------------+------------+-------------------+--------------+
| exam_id | exam_date  | description | count_fail | count_attended | count_pass | count_distinction | final_result |
+---------+------------+-------------+------------+----------------+------------+-------------------+--------------+
|       1 | 2016-06-01 | Exam 1      |          1 |              1 |          0 |                 0 |  Distinction |
|       2 | 2016-06-06 | Exam 2      |          0 |              0 |          0 |                 2 |  Fail        |
|       3 | 2016-06-07 | Exam 3      |          0 |              0 |          1 |                 1 |  Distinction |
|       4 | 2016-06-10 | Exam 4      |          0 |              1 |          1 |                 0 |  Pass        |
+---------+------------+-------------+------------+----------------+------------+-------------------+--------------+

请问这是怎么回事？ WHEN 条件是如何不满足的(很明显，@ 变量中正在进行某种计算，因为输出并不全是乱码)？这只是更大的计划的一个小比例的说明，不幸的是我无法详细说明。但是，我真的想避免使用嵌套查询并尽可能坚持使用 JOINS...

如果收到您的意见和建议，我将非常感激。

非常感谢!

编辑

数据库和查询的 SQL Fiddle 是 here

预期的输出是:

+---------+------------+-------------+------------+----------------+------------+-------------------+--------------+
| exam_id | exam_date  | description | count_fail | count_attended | count_pass | count_distinction | final_result |
+---------+------------+-------------+------------+----------------+------------+-------------------+--------------+
|       1 | 2016-06-01 | Exam 1      |          1 |              1 |          0 |                 0 |  Fail        |
|       2 | 2016-06-06 | Exam 2      |          0 |              0 |          0 |                 2 |  Distinction |
|       3 | 2016-06-07 | Exam 3      |          0 |              0 |          1 |                 1 |  Pass        |
|       4 | 2016-06-10 | Exam 4      |          0 |              1 |          1 |                 0 |  Attended    |
+---------+------------+-------------+------------+----------------+------------+-------------------+--------------+

原因是:

第 1 行:至少有一门科目取得了可能的最低成绩(不及格)，因此总体结果必须是“不及格”

第 2 行:该考试中的两个科目均已通过优异成绩，因此总体结果应为“优异”

第 3 行:1 次及格，1 次优异。这个结果不能算是一个区别，但也不是“失败”。由于本次考试的两门科目均已通过(尽管其中一科未取得优异成绩)，因此结果应为“通过”。

第 4 行: 1 人参加，1 人通过。计数不足以得出总体结果为“通过”的结论(因为“参加”是不确定的)。因此，总体结果只能被视为“已参加”(直到该考试中的所有科目均获得“通过”，或者至少有一个“未通过”，从而导致总体结果被称为“失败”。就目前情况而言，“优异”现在是不可能实现的，因为该考试中至少有一门科目已经以较低的及格分数注册)

Answer 1

这是一个solution as shown in SQLFiddle没有用户定义的变量

SELECT exams.exam_id, exam_date, description, 
SUM(result='Fail')          AS count_fail, 
SUM(result='Attended')      AS count_attended, 
SUM(result='Pass')          AS count_pass, 
SUM(result='Distinction')   AS count_distinction,
CASE 
    WHEN (SUM(result='Fail') >= SUM(result='Attended') AND SUM(result='Fail') >= 1)
        THEN 'Fail'
    WHEN (SUM(result='Attended') >= SUM(result='Pass')  AND SUM(result='Attended') >= 1)
        THEN 'Attended'
    WHEN (SUM(result='Pass')  >= SUM(result='Distinction') AND SUM(result='Pass') >= 1)    
        THEN 'Pass'
    ELSE 
        'Distinction'
    END AS final_result FROM exams 
INNER JOIN subjects ON exams.exam_id = subjects.exam_id
GROUP BY exam_id