我有2张桌子的学生和出勤率。我需要将两个表都连接起来,并得到结果,例如总缺勤人数和特定班级和班级名称的学生人数。我需要一个查询才能获得以下结果
tbl_student:
admission_no(PK) student_name student_class
345 John X A
352 Sachin X A
322 Steve IX A
123 Pinky X A
343 Rose IX A
tbl_admission:
admission_no(FK) date_absent
354 2015-03-30
123 2015-03-30
322 2015-03-30
结果应该是这样的:
Date_absent total_absent total_students student_class
2015-03-30 2 3 X A
2015-03-30 1 2 IX A
最佳答案
您可以执行以下操作以在单个查询中对其进行管理:
SELECT date_absent, total_absent, total_students, a1.student_class
FROM (
SELECT date_absent, count( tbl_admission.admission_no ) AS total_absent, tbl_student.student_class
FROM `tbl_admission`
JOIN `tbl_student` ON tbl_student.admission_no = tbl_admission.admission_no
GROUP BY tbl_student.student_class
)a1
JOIN (
SELECT count( tbl_student.admission_no ) AS total_students, tbl_student.student_class
FROM tbl_student
GROUP BY tbl_student.student_class
)a2
ON a1.student_class = a2.student_class
ORDER BY `a1`.`student_class` DESC
关于mysql - 在一个查询中获得总数和缺勤总数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29361093/