过滤尚未通过科目的学生 [英] Filter students that have not passed a subject yet

问题描述

我有一个 MySql 表，它有以下值:

I have a MySql table and it has following values:

表名'结果'

Reg.No  SubjectCode  Attempt  Pass/Fail
112108  CMIS 1113    1        fail
112110  CMIS 1114    1        pass
112119  CMIS 1114    1        fail
112108  CMIS 1113    2        fail
112107  CMIS 1113    1        fail
112108  CMIS 1113    3        pass

学生可以多次尝试通过该科目.学生必须通过每个科目才能获得学位.一些学生在第一次尝试中就通过了.有些需要超过 3 次尝试.但是学生可以尝试直到他/她通过.但有些仍然失败.所以我想得到仍然无法通过该科目的学生的Reg.No.
例如:112119 和 112107 仍然无法通过他们的科目.
我无法针对此问题编写查询.

Students can have several attempts to pass the subject. Student should pass each subjects to get the degree. Some student pass in first attempt. Some take more than 3 attempt. However student can try until he/she pass. But some still remain fail. So I want to get the Reg.No of students who are still unable to pass the subject.
Eg.: 112119 and 112107 are still unable to pass their subject.
I was unable to write a query for this problem.

推荐答案

我建议使用聚合:

SELECT `Reg.No`, SubjectCode, SUM(`Pass/Fail` = 'Pass')
FROM results
GROUP  BY `Reg.No`, SubjectCode
HAVING SUM(`Pass/Fail` = 'Pass') = 0;

HAVING 子句计算每个学生和课程的结果数，其中最后一列是 'Pass'.在 MySQL 中，布尔值在数字上下文中被视为整数，true 为 1.因此，sum(Pass/Fail='Pass') 计算学生通过了课程.= 0 表示该学生从未通过该课程.

The HAVING clause counts the number of results for each student and course where the last column is 'Pass'. In MySQL, booleans are treated as integers in a numeric context, with true being 1. So, sum(Pass/Fail= 'Pass') counts the number of times a student passed the course. The = 0 says the student never passed the course.

建议不要在列名中放置特殊字符，例如 / 和 ..这需要对列进行转义，并且只会使代码更难编写，因为它充满了反引号.

As a suggestion, don't put special characters such as / and . in column names. That requires escaping the columns and just makes the code harder to write because it is filled with backticks.

这篇关于过滤尚未通过科目的学生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！