GROUP BY and HAVING
分组行 到 buckets, aggregate 每个分组, and 筛选分组使用 HAVING.
你将学到
- 如何 GROUP BY partitions 行到 分组
- 组合 GROUP BY 使用 aggregate 函数
- 筛选分组使用 HAVING 对比筛选行 使用 WHERE
- 分组通过多个列
- 理解 logical 顺序的 SQL clauses
概念讲解
GROUP BY
GROUP BY takes the rows of a table and partitions them into groups that share the same value in the grouping column(s). You then apply an aggregate function to each group, getting one output row per group. For example, GROUP BY 分类 on a products table produces one row per category, and you can compute COUNT(*) or AVG(price) for each.
WHERE vs HAVING
WHERE filters individual rows before grouping. HAVING filters groups after grouping and aggregation. This is a crucial distinction: use WHERE to exclude rows you do not want in any group, and HAVING to exclude groups based on their aggregate value. For example, "show 分类使用平均 price above 50" uses HAVING on AVG(price).
Grouping by Multiple Columns
You can group by more than one column. GROUP BY 分类, brand creates a group for every (category, brand) combination. The result has one row per unique combination. The order of columns in GROUP BY does not change the result, but it affects the default sort order in some databases.
Logical Clause Order
Although you write SQL as SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT, the database evaluates it in a different order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Understanding this explains why you cannot use column aliases from SELECT in WHERE or GROUP BY — they have not been computed yet at that stage.
Common Pattern: Top N per Group
A frequent reporting need is " 顶部 3 products 通过 sales in 每个分类." This requires either a window function (ROW_NUMBER() OVER (PARTITION BY ...)) or a self-join. It is a classic SQL interview question.
示例代码
-- Revenue and order count per category
SELECT
category,
COUNT(*) AS order_count,
SUM(total) AS revenue,
AVG(total) AS avg_order
FROM orders
WHERE status = 'completed'
GROUP BY category
ORDER BY revenue DESC;
-- Only categories with more than 50 orders
SELECT category, COUNT(*) AS num_orders
FROM orders
GROUP BY category
HAVING COUNT(*) > 50
ORDER BY num_orders DESC;
-- Group by two columns: category and month
SELECT
category,
DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY category, month
ORDER BY month, category;
Three 模式: 基础 GROUP BY 使用 aggregates, HAVING 到筛选分组, and 分组通过多个列 包含 date truncation.
动手试一试
- SQL Formatter
格式 GROUP BY and HAVING 查询 for readability.
- CSV Formatter
导出分组 report 数据 as CSV for spreadsheets.
常见错误
错误写法
Using HAVING for 条件那个应该是 in WHERE
正确做法
HAVING 筛选分组之后 aggregation. 如果条件 applies 到 individual 行, put 它 in WHERE — 它是 更多 efficient 因为行 是排除之前分组.
错误写法
选择列 那个是 不 in GROUP BY or aggregated
正确做法
每个列 in SELECT 必须要么是 in GROUP BY or wrapped in aggregate 函数. 否则数据库 cannot decide 哪个值 到 show for 分组.
练习 GROUP BY and HAVING
12 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。