是否可以基于两列的组合执行计数?
我有三张桌子:
学生:Student id | Student name | Gender | Dob |
班级注册:Student id | Class id
这两列构成复合主键。
课程:Class id | Class name | Class day
我想计算一下每个班每个性别的学生人数。
到目前为止我提出了这个问题:
SELECT class_name AS 'class Name',
Child_gender AS 'Gender',
COUNT(student_id) AS 'Count'
FROM student,classRegister,classes
WHERE student.student_id=classRegister.student_id AND
classRegister.class_id=classes.class_id
GROUP BY class_name,student_gender
ORDER BY class_name;
但它似乎给了我一些学生重复的结果。
所以,我可以修改这个查询来计算
student_id
和class_id
组合的实例数吗?我对MySQL还很陌生,如果这对每个人都是基本的,我很抱歉
最佳答案
试试这个:
SELECT c.class_name AS 'class Name', s.Child_gender AS 'Gender',
COUNT(s.student_id) AS 'Count'
FROM student s
INNER JOIN classRegister cr ON s.student_id = cr.student_id
INNER JOIN classes c ON cr.class_id = c.class_id
GROUP BY s.Child_gender, s.class_id, c.class_name
ORDER BY c.class_name