mysql - MySQL，计算两个或多个表中每个 ID 的总数

Question

我有 3 个单独的学生(table_student)，每个学生都上过 3 节课(table_class_1, table_class_2, table_class_3)

下图是学生表 (table_student)、类 1 (table_class_1)、类 2(table_class_2)和类 3的列表(table_class_3)以及每个类表的总结果。

我的目标是计算每个学生进入每个班级的次数。所以我的预期结果如下图所示

到目前为止我能做的是使用联合，但结果不是我所期望的

SELECT COUNT(B.student_id) AS count_class_1, NULL, NULL FROM table_student A
INNER JOIN table_class_1 B ON A.id = B.student_id
WHERE B.student_id = 1

UNION

SELECT NULL, COUNT(B.student_id) AS count_class_2, NULL  FROM table_student A
INNER JOIN table_class_2 B ON A.id = B.student_id
WHERE B.student_id = 1

UNION

SELECT NULL, NULL, COUNT(B.student_id) AS count_class_3 FROM table_student A
INNER JOIN table_class_3 B ON A.id = B.student_id
WHERE B.student_id = 1

标签： mysql

Answer 1

您的表格未正确规范化，这是您面临这一挑战的部分原因。有如下三个表：

table_student (id, student_name),
table_class (id, class_name),
table_student_class(id, table_student_id, table_class_id)

将数据填充到表中并使用以下语句获取学生出勤率。

select table_student_id, table_class_id, Count(id) as class_count 
from table_student_class 
group by table_student_id, table_class_id

结果将是每个学生的课堂出勤人数。