前面两章的查询，返回的都是一行行「明细」。可后台那些数字——「全站一共几篇作品」「唐诗平均浏览量多少」——要的不是明细，是把很多行压成一个数。这一章教你用聚合函数把数据「加起来看」：COUNT 数数、SUM 求和、AVG 求平均、MIN/MAX 找极值，再用 GROUP BY 按朝代、按分类分别统计，最后用 HAVING 把「篇数太少」的组筛掉。学完这章，数据看板你就自己能做了。
-- 不用聚合：把 6 行明细全给你，想看总数还得自己数
SELECT id, title FROM article;
-- 用聚合：直接给答案
SELECT COUNT(*) AS 作品总数 FROM article;SELECT COUNT(*) AS 全部书评,
COUNT(parent_id) AS 是回复的书评
FROM comment;SELECT COUNT(*) AS 作品总数,
COUNT(category_id) AS 有分类的作品,
COUNT(*) - COUNT(category_id) AS 缺分类的作品
FROM article;SELECT SUM(views) AS 总浏览量,
ROUND(AVG(views), 1) AS 平均浏览量,
MIN(views) AS 最少浏览量,
MAX(views) AS 最多浏览量
FROM article;SELECT AVG(views) AS 原始平均值,
ROUND(AVG(views), 1) AS 保留一位小数,
ROUND(AVG(views)) AS 取整,
ROUND(SUM(likes) / COUNT(*), 1) AS 平均点赞数
FROM article;SELECT dynasty AS 朝代,
COUNT(*) AS 篇数,
ROUND(AVG(views), 1) AS 平均浏览量,
SUM(views) AS 总浏览量
FROM article
GROUP BY dynasty
ORDER BY 篇数 DESC;SELECT dynasty AS 朝代,
COUNT(*) AS 篇数,
ROUND(AVG(views), 1) AS 平均浏览量
FROM article
WHERE views > 200
GROUP BY dynasty
ORDER BY 篇数 DESC;SELECT dynasty AS 朝代,
COUNT(*) AS 篇数
FROM article
GROUP BY dynasty
HAVING 篇数 >= 2;SELECT dynasty AS 朝代,
COUNT(*) AS 篇数,
ROUND(AVG(views), 1) AS 平均浏览量
FROM article
WHERE status = 1 -- ① 先筛行：只看已发布的
GROUP BY dynasty -- ② 再分组
HAVING ROUND(AVG(views), 1) > 300; -- ③ 最后筛组：平均浏览量要够高SELECT COUNT(*) AS 总行数,
COUNT(parent_id) AS COUNT列会跳过NULL,
SUM(parent_id) AS SUM全是NULL时返回NULL,
IFNULL(SUM(parent_id), 0) AS 用IFNULL兜底成0
FROM comment;SELECT id,
IFNULL(parent_id, 0) AS 父评论ID_IFNULL,
COALESCE(parent_id, '顶层评论') AS 评论层级,
COALESCE(parent_id, 0) AS 父评论ID_COALESCE
FROM comment;SELECT category_id AS 分类ID,
COUNT(*) AS 篇数
FROM article
GROUP BY category_id
ORDER BY category_id;SELECT c.id AS 分类ID,
c.name AS 分类名,
COUNT(a.id) AS 篇数
FROM category c
LEFT JOIN article a ON a.category_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;-- 第 1 题
SELECT COUNT(*) AS 总篇数,
SUM(views) AS 总浏览量,
ROUND(AVG(views), 1) AS 平均浏览量
FROM article;
-- 第 2 题
SELECT is_recommend AS 是否推荐,
COUNT(*) AS 篇数,
ROUND(AVG(views), 1) AS 平均浏览量
FROM article
GROUP BY is_recommend
ORDER BY is_recommend DESC;
-- 第 3 题（红影赏析的库，表名换成 movie）
SELECT genre AS 类型,
COUNT(*) AS 电影数,
ROUND(AVG(rating), 1) AS 平均评分
FROM movie
GROUP BY genre
HAVING COUNT(*) >= 2
ORDER BY 电影数 DESC;第 3 题的关键：「电影数不少于 2 部」这个条件是对「组」提的，而分组发生在 WHERE 之后，所以 WHERE 里根本没有 COUNT(*) 可用（写了会报 Invalid use of group function）。判断口诀：条件里带不带聚合函数——不带就放 WHERE，带了就放 HAVING。