MySQL连表查询在另一张表某个字段的数量以及和

问题描述：

连表查询在另一张表某个字段的数量以及和

---

解决方案：

-- 查询教师列表以及教师学生总数以及学生总成绩和
SELECT t.*,COUNT(s.id) as stu_num,SUM(s.score) as score_total FROM teacher t LEFT JOIN student s ON s.id = t.teacher_id WHERE ... GROUP BY t.id 
 
-- 查询教师的学生总成绩大于500的
SELECT t.*,COUNT(s.id) as stu_num,SUM(s.score) as score_total FROM teacher t LEFT JOIN student s ON s.id = t.teacher_id WHERE ... GROUP BY t.id HAVING score_total > 500
 
-- 查询教师学生总数大于10 的
SELECT t.*,COUNT(s.id) as stu_num,SUM(s.score) as score_total FROM teacher t LEFT JOIN student s ON s.id = t.teacher_id WHERE ... GROUP BY t.id HAVING stu_num > 10
 
-- 查询教师的学生成绩在200到500之间，学生总数在10到20区间的
SELECT t.*,COUNT(s.id) as stu_num,SUM(s.score) as score_total FROM teacher t LEFT JOIN student s ON s.id = t.teacher_id WHERE ... GROUP BY t.id HAVING (score_total BETWEEN 200 AND 500 ) AND (stu_num BETWEEN 10 AND 20)