题目:
查询1601041班选课人数大于10人的课程,展示出课程编号和该班级的选课人数。
本题的两张表——user_module和user。其中user为学生表,user_module为学生选课的中间表(module表本题中不涉及,就不展示了),结构如下:
题解:
第一步:先查询课程的选课人数,并按课程编号展示出来:
使用聚合函数GROUP BY语句,可以将结果按照某一字段坍缩,此时便可使用count()来统计该字段的重复次数:
SELECT module_id,count(*)
FROM user_module
GROUP BY module_id
可以查询出五门课以及对应的选课人数,如图:
第二步:多表联查,左外连接user表,注意不能在group by后进行left join,因为username字段被坍缩了后无法外键连接到user表,故应该先进行左外连后再使用聚合函数:
SELECT module_id,count(*)
FROM user_module
LEFT JOIN user ON user_module.username=user.username
WHERE user.class_id=1601041
GROUP BY module_id
可看出只有两门课有1601041班的同学选了课,如图:
第三步:根据题意,过滤掉人数少于10的课程。这里应用HAVING语句而不是WHERE,原因是我们在筛选时使用了count()这样的函数进行了计算(使用sum(),avg()等等同理),因此只能使用HAVING:
SELECT module_id,count(*)
FROM user_module
LEFT JOIN user ON user_module.username=user.username
WHERE user.class_id=1601041
GROUP BY module_id
HAVING count(*)>10
最后就能得出本题的结果,如图:
补:左外连也可以简写成
SELECT module_id,count(*)
FROM user_module um,user u
WHERE um.username=u.username and u.class_id=1601041
GROUP BY module_id
HAVING count(*)>10