MySQL 查询 Group wise Summary

Question

我的 table 是这样的

Attendance
----------

Studentid | Date       | Status
--------------------------------
1         | 2017-01-01 | Present
1         | 2017-01-02 | Present
1         | 2017-01-03 | Absent
1         | 2017-01-04 | Presnt
2         | 2017-01-01 | Absent
2         | 2017-01-02 | Present
2         | 2017-01-03 | Absent
2         | 2017-01-04 | Presnt

我想写一个 Mysql 查询来得到这样的输出。

StudentID | PresentCount  | AbsentCount
1         |       3       |     1
2         |       2       |     2

Answer 1

这应该有效:

SELECT StudentId, SUM(Status='Present') as PresentCount, SUM(Status='Absent') AS AbsentCount FROM Attendance GROUP BY StudentId;

如果您的字段值对 'Absent' 和 'Present' 的拼写不同，也会出现问题。