MySQL 查询 : 3 tests passed, 1 失败

Question

我正在尝试编写一个查询，以提取已通过测试 1 到 3 且未通过测试 4 的学生。

学生可以重新参加考试，所以可能会有失败的记录，然后是通过的记录，例如下面的 student_id = 2。

像这样的表格设置 -

test_id | student_id | status  | completed_on
--------+------------+---------+------------
 1      |    1       | passed  | 2018-03-24 
 2      |    1       | passed  | 2018-03-25 
 3      |    1       | passed  | 2018-03-26 
 4      |    1       | failed  | 2018-03-27 
 1      |    2       | failed  | 2018-03-24 
 1      |    2       | passed  | 2018-03-25 
 2      |    2       | passed  | 2018-03-26 
 3      |    2       | passed  | 2018-03-27 
 4      |    2       | failed  | 2018-03-27 

在这种情况下，查询应该同时提取 student_id 1 和 2

我试过了，但显然没用 -

select * 
from table 
where (test_id = 1 and status = 'passed') 
and (test_id = 2 and status = 'passed') 
and (test_id = 3 and status = 'passed') 
and (test_id = 4 and status = 'failed')

Answer 1

Demo

SELECT count(Z.Test_ID), Z.student_ID 
FROM (SELECT distinct student_ID, test_ID, Status 
      FROM table) Z
WHERE (Z.Status = 'Passed' and Z.test_ID in (1,2,3,4))
   OR (Z.status = 'Failed' and Z.test_ID = 4)
GROUP BY Z.Student_ID
HAVING count(Z.Test_ID)  = 4;

这首先确保我们只有每个学生、状态和 test_ID 的不同记录。 (导出表Z)

然后我们评估测试 1、2、3、4 中存在多少次通过以及测试 4 中存在多少次失败。如果计数不是 4，那么我们知道他们没有通过测试 1- 3 并未通过 4，或者他们也通过了测试 4。