此页面需较新浏览器(支持 SharedArrayBuffer)。请升级 Chrome、Edge、Firefox 或 Safari 至最新版本。
用 GROUP BY 分组
像 AVG 这样的聚合函数,通常把整张表汇总成一个数。GROUP BY 会把行分成共享同一个值的组,并为每个组给出一行汇总:
SELECT form, COUNT(*) AS n, ROUND(AVG(score), 2) AS avg_score
FROM student
GROUP BY form;
这样每个 form 得到一行,显示该 form 的人数和平均分。
用 HAVING 筛选组
WHERE 在分组之前筛选行。要筛选组本身 —— 用聚合函数 —— 就用 HAVING:
SELECT form, COUNT(*) AS n
FROM student
GROUP BY form
HAVING COUNT(*) >= 3;
这样只保留有 3 名或更多学生的 form。(WHERE 不能测试 COUNT(*),而 HAVING 可以。)
范例:先 WHERE,再 GROUP BY
问题。 按班级显示总分,并计算得分达到或超过 70 的学生分数总和,作为 total。
答案。 WHERE 在分组之前过滤行,因此 Tom(64)永远不会到达 11B 组:
SELECT form, SUM(score) AS total
FROM student
WHERE score >= 70
GROUP BY form;
form | total
11A | 271
11B | 72
子句必须始终按此顺序排列:SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …。
常见错误
- 用
HAVING过滤分组,用WHERE过滤行。HAVING COUNT(*) >= 3用于测试分组;WHERE score >= 70用于测试行。 - 子句顺序。 将
GROUP BY放在WHERE之前会导致语法错误。 - 聚合函数旁的普通列必须包含在
GROUP BY中。 在SELECT form, name, COUNT(*) … GROUP BY form中,每个分组内的name存在多个值,因此结果毫无意义。大多数数据库会拒绝该查询;但运行此沙箱的 SQLite 会为每个分组静默选取一个名称,故无报错并不意味着查询正确。
难度 3/5 · 今日首次完成可获 200 金币及少量额外奖励
针对每个form,显示其班级、学生人数作为n,以及平均分(2位小数)作为avg_score。按form分组。
点击“运行”查看此处输出。
难度 3/5 · 今日首次完成可获 200 金币及少量额外奖励
显示每个 form 及其学生人数 n,但只保留人数在 3 或以上的 form。用 HAVING。
点击“运行”查看此处输出。
难度 3/5 · 今日首次完成可获 200 金币及少量额外奖励
对于每个form,仅统计得分**≥80**的学生人数,作为n。初始语句统计所有人:添加一个WHERE。(没有此类学生的班级将从结果中剔除。)
点击“运行”查看此处输出。