SELECT * FROM classes;
id name
1 一班
2 二班
3 三班
4 四班
SELECT * FROM students;
id class_id name gender
1 1 小明 M
2 1 小红 F
3 1 小军 M
4 1 小米 F
5 2 小白 F
6 2 小兵 M
7 2 小林 M
8 3 小新 F
9 3 小王 M
10 3 小丽 F
-
找出每个班级的名称及其对应的女同学数量
#正确
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
and s.gender = 'F'
group by c.name
或者
#错误
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
where s.gender = 'F'
group by c.name
2.找出一班的同学总数
#正确
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
where c.name = '一班'
group by c.name
或者
#错误
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
and c.name = '一班'
group by c.name
总结:在left join语句中,左表过滤必须放where条件中,右表过滤必须放on条件中