mysql - 对同一列进行多次计数并插入到另一个表中

Question

我有一个包含三列的学生表

1. Student Name
2. Class Name
3. Test result

一名学生参加了多次考试，结果各不相同。我正在尝试将数据放入另一个表中

1. Stundent Name+CLass Name ( Concatenated )
2. Pass (No of tests Passed)
3. Fail (No of tests failed)
4. Absent (No of tests Absent)

我用

select count(*)
from Student 
where Result in ('Passed')
group by StuName, ClassName;

获取每个 stu+class 组合的及格科目数。对于失败和缺席的测试也是如此。

我应该如何修改代码来插入表二？

Answer 1

MySQL 支持内联 IF 语句，

SELECT  CONCAT(StudentName, ' ', ClassName) Student_Class,
        SUM(test = 'PASSED') totalPass,
        SUM(test = 'Failed') totalFailed,
        SUM(test = 'Absent') totalAbsent
FROM    student
GROUP   BY CONCAT(StudentName, ' ', ClassName)

当你想插入上面查询的结果时，使用INSERT INTO..SELECT语句，

INSERT  INTO tableNAME(col1, totalPass, totalFailed, totalAbsent)
SELECT  CONCAT(StudentName, ' ', ClassName) Student_Class,
        SUM(test = 'PASSED') totalPass,
        SUM(test = 'Failed') totalFailed,
        SUM(test = 'Absent') totalAbsent
FROM    student
GROUP   BY CONCAT(StudentName, ' ', ClassName)