问题描述:

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


解决方案:

-- 查询教师列表以及教师学生总数以及学生总成绩和
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)

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐