MySQL 如何在MySQL中获取一年中出席和缺席学生的数量？

在教育领域，了解学生的出勤状况是非常重要的。教师们需要掌握学生在一年中有效上课的次数和缺勤次数，以了解学生的学习情况并及时针对不良习惯进行干预。在MySQL数据库中，可以通过一些简单的查询语句来获取这些数据。

阅读更多：MySQL 教程

1. 创建学生表

首先，让我们创建一个名为“students”的表来存储学生的信息，其中包含学生的ID、姓名、出生日期和性别信息。

CREATE TABLE students (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  birthdate DATE,
  gender ENUM('M', 'F') NOT NULL
);

2. 创建考勤记录表

接着，创建一个名为“attendance”的表来存储学生的考勤记录。在这个表中，我们需要记录学生的出勤日期和状态，其中1表示出席，0表示缺席。每个出勤日期可以对应多个学生的考勤状态。

CREATE TABLE attendance (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  date DATE,
  status TINYINT(1) NOT NULL,
  student_id INT(6) UNSIGNED,
  FOREIGN KEY (student_id) REFERENCES students(id)
);

3. 填充数据

现在，我们需要向这两个表中填充一些数据。让我们先向学生表中添加一些学生信息。

INSERT INTO students (name, birthdate, gender)
VALUES ('小明', '2001-01-01', 'M'),
       ('小红', '2002-02-02', 'F'),
       ('小亮', '2003-03-03', 'M'),
       ('小花', '2004-04-04', 'F');

接着，向考勤记录表中添加一些数据。在这个例子中，我们将学生的考勤状态设置为随机值。

INSERT INTO attendance (date, status, student_id)
VALUES ('2022-01-01', RAND() > 0.5, 1),
       ('2022-01-01', RAND() > 0.5, 2),
       ('2022-01-01', RAND() > 0.5, 3),
       ('2022-01-01', RAND() > 0.5, 4),
       ('2022-01-02', RAND() > 0.5, 1),
       ('2022-01-02', RAND() > 0.5, 2),
       ('2022-01-02', RAND() > 0.5, 3),
       ('2022-01-02', RAND() > 0.5, 4),
       ('2022-01-03', RAND() > 0.5, 1),
       ('2022-01-03', RAND() > 0.5, 2),
       ('2022-01-03', RAND() > 0.5, 3),
       ('2022-01-03', RAND() > 0.5, 4),
       ('2022-01-04', RAND() > 0.5, 1),
       ('2022-01-04', RAND() > 0.5, 2),
       ('2022-01-04', RAND() > 0.5, 3),
       ('2022-01-04', RAND() > 0.5, 4),
       ('2022-01-05', RAND() > 0.5, 1),
       ('2022-01-05', RAND() > 0.5, 2),
       ('2022-01-05', RAND() > 0.5, 3),
       ('2022-01-05', RAND() > 0.5, 4);

4. 获取出席和缺席数量

现在，我们可以使用以下查询语句来获取指定时间范围内每个学生的出席和缺席数量。

SELECT 
  students.id, 
  students.name, 
  SUM(CASE WHEN attendance.status = 1 THEN 1 ELSE 0 END) AS attendances, 
  SUM(CASE WHEN attendance.status = 0 THEN 1 ELSE 0 END) AS absences
FROM 
  students 
  LEFT JOIN attendance ON students.id = attendance.student_id 
                        AND attendance.date BETWEEN '2022-01-01' AND '2022-01-05'
GROUP BY 
  students.id, students.name;

上述查询语句将两个表进行了联接，并使用了SUM与CASE表达式对每个学生的出勤和缺席进行了统计。其中，LEFT JOIN确保即使某些学生在考勤记录表中没有对应数据也可以被查询出来。最后，GROUP BY保证了结果是按照每个学生的ID和姓名进行分组。