是否可以基于两列的组合执行计数?
我有三张桌子:
学生:
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_idclass_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

07-24 09:51