GROUP BY：按列分组
为什么需要分组
上一节学习了聚合函数，但只能对整个表进行统计：
sql
-- 所有订单的总数
SELECT COUNT(*) FROM orders;
-- 结果：7（一个数字）如果想知道每个客户的订单数量呢？这就需要 GROUP BY。
GROUP BY 基础
GROUP BY 将数据按指定列的值分成多个组，对每组分别进行聚合。
语法：
sql
SELECT column, aggregate_function(column)
FROM table
GROUP BY column;示例：每个客户的订单数量
sql
SELECT
customer_name,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_name;结果：
text
customer_name | order_count
---------------+-------------
Alice | 3
Bob | 2
Charlie | 1
Diana | 1工作原理：
- 按
customer_name将数据分成 4 组 - 对每组分别执行
COUNT(*) - 返回 4 行结果（每组一行）
GROUP BY 的规则
规则 1：SELECT 中的非聚合列必须在 GROUP BY 中
sql
-- 错误：email 没有在 GROUP BY 中
SELECT customer_name, email, COUNT(*)
FROM orders
GROUP BY customer_name;错误信息（PostgreSQL）：
text
ERROR: column "orders.email" must appear in the GROUP BY clause
or be used in an aggregate function为什么？ 因为每组可能有多个不同的 email，数据库不知道应该显示哪一个。
正确写法：
sql
-- 方式 1：只选择分组列和聚合结果
SELECT customer_name, COUNT(*)
FROM orders
GROUP BY customer_name;
-- 方式 2：把 email 也加入 GROUP BY
SELECT customer_name, email, COUNT(*)
FROM orders
GROUP BY customer_name, email;规则 2：聚合函数可以不在 GROUP BY 中
sql
-- 正确：COUNT、SUM、AVG 等聚合函数不需要在 GROUP BY 中
SELECT
customer_name,
COUNT(*) AS order_count,
SUM(amount) AS total_spent,
AVG(amount) AS average_order
FROM orders
WHERE amount IS NOT NULL
GROUP BY customer_name;结果：
text
customer_name | order_count | total_spent | average_order
---------------+-------------+-------------+---------------
Alice | 3 | 1290.00 | 430.000000
Bob | 2 | 75.00 | 37.500000
Charlie | 1 | 300.00 | 300.000000
Diana | 1 | |常见分组场景
场景 1：统计每个类别的数量
sql
-- 每种产品被订购了多少次
SELECT
product,
COUNT(*) AS times_ordered
FROM orders
GROUP BY product
ORDER BY times_ordered DESC;场景 2：每组的总和与平均值
sql
-- 每个客户的消费统计
SELECT
customer_name,
COUNT(*) AS orders,
SUM(amount) AS total,
ROUND(AVG(amount), 2) AS average
FROM orders
WHERE amount IS NOT NULL
GROUP BY customer_name
ORDER BY total DESC;场景 3：找出每组的最大/最小值
sql
-- 每个客户的最大和最小订单金额
SELECT
customer_name,
MIN(amount) AS smallest_order,
MAX(amount) AS largest_order
FROM orders
WHERE amount IS NOT NULL
GROUP BY customer_name;结果：
text
customer_name | smallest_order | largest_order
---------------+----------------+---------------
Alice | 10.00 | 1200.00
Bob | 25.00 | 50.00
Charlie | 300.00 | 300.00按多列分组
可以按多个列分组，创建更细粒度的组：
sql
-- 创建更丰富的订单表
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_name VARCHAR(50),
product_category VARCHAR(50),
product VARCHAR(50),
amount DECIMAL(10, 2),
order_date DATE
);
INSERT INTO orders (customer_name, product_category, product, amount, order_date) VALUES
('Alice', 'Electronics', 'Laptop', 1200.00, '2026-01-15'),
('Alice', 'Electronics', 'Mouse', 25.00, '2026-01-16'),
('Alice', 'Furniture', 'Desk', 300.00, '2026-01-17'),
('Bob', 'Electronics', 'Keyboard', 80.00, '2026-01-18'),
('Bob', 'Furniture', 'Chair', 150.00, '2026-01-19'),
('Charlie', 'Electronics', 'Monitor', 250.00, '2026-01-20'),
('Charlie', 'Furniture', 'Lamp', 50.00, '2026-01-21');按客户和类别分组：
sql
SELECT
customer_name,
product_category,
COUNT(*) AS items,
SUM(amount) AS total
FROM orders
GROUP BY customer_name, product_category
ORDER BY customer_name, product_category;结果：
text
customer_name | product_category | items | total
---------------+------------------+-------+--------
Alice | Electronics | 2 | 1225.00
Alice | Furniture | 1 | 300.00
Bob | Electronics | 1 | 80.00
Bob | Furniture | 1 | 150.00
Charlie | Electronics | 1 | 250.00
Charlie | Furniture | 1 | 50.00每个 (customer_name, product_category) 组合对应一行。
GROUP BY 与 WHERE
WHERE 在分组之前过滤行：
sql
-- 只统计 Electronics 类别的订单
SELECT
customer_name,
COUNT(*) AS electronics_orders,
SUM(amount) AS total
FROM orders
WHERE product_category = 'Electronics'
GROUP BY customer_name;结果：
text
customer_name | electronics_orders | total
---------------+--------------------+--------
Alice | 2 | 1225.00
Bob | 1 | 80.00
Charlie | 1 | 250.00执行顺序：FROM → WHERE（过滤行）→ GROUP BY（分组）→ SELECT（聚合）
GROUP BY 与 ORDER BY
分组结果可以排序：
sql
-- 按总消费降序排列
SELECT
customer_name,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_name
ORDER BY total_spent DESC;结果：
text
customer_name | total_spent
---------------+-------------
Alice | 1525.00
Bob | 230.00
Charlie | 300.00可以按聚合结果或分组列排序。
NULL 在 GROUP BY 中的行为
NULL 值会被当作一个单独的组：
sql
-- 假设有 NULL 值
INSERT INTO orders (customer_name, product_category, product, amount, order_date) VALUES
(NULL, 'Electronics', 'Cable', 10.00, '2026-01-22');
SELECT
customer_name,
COUNT(*) AS orders
FROM orders
GROUP BY customer_name;结果：
text
customer_name | orders
---------------+--------
Alice | 3
Bob | 2
Charlie | 2
| 1最后一行是 NULL 组。
GROUP BY 中的表达式
可以按表达式分组：
sql
-- 按订单日期的年月分组
SELECT
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
COUNT(*) AS orders,
SUM(amount) AS total
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;或者更简洁（PostgreSQL 和 MySQL 8.0+ 支持）：
sql
-- 按年月分组
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS orders,
SUM(amount) AS total
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;常见错误
错误 1：忘记 GROUP BY 非聚合列
sql
-- 错误
SELECT customer_name, COUNT(*) FROM orders;错误 2：在 WHERE 中使用聚合函数
sql
-- 错误：WHERE 不能用聚合函数
SELECT customer_name, COUNT(*)
FROM orders
WHERE COUNT(*) > 2 -- 错误！
GROUP BY customer_name;正确做法：使用 HAVING（下一节）
sql
SELECT customer_name, COUNT(*)
FROM orders
GROUP BY customer_name
HAVING COUNT(*) > 2;错误 3：对 GROUP BY 的列使用聚合
sql
-- 错误：customer_name 既在 GROUP BY 又在 COUNT 中
SELECT customer_name, COUNT(customer_name)
FROM orders
GROUP BY customer_name;这不会报错，但没有意义。应该是：
sql
SELECT customer_name, COUNT(*)
FROM orders
GROUP BY customer_name;练习
使用更新后的 orders 表完成以下练习：
基本分组：查询每个产品类别的订单数量。
分组聚合：查询每个客户的总消费金额，按金额降序排序。
多列分组：查询每个客户在每个类别的订单数量和总金额。
带过滤的分组：查询金额大于 100 的订单中，每个客户的订单数量。
最值分组：查询每个产品类别的最低和最高订单金额。
表达式分组：查询每个月的订单数量和总销售额。
NULL 处理：插入一些 customer_name 为 NULL 的订单，观察 GROUP BY 的行为。
复杂查询：查询每个客户在 Electronics 类别的订单数量和总金额，只显示有订单的客户。
完成练习后，打开本章答案页核对。
下一步
学会了 GROUP BY 分组。下一节学习 HAVING：如何过滤分组结果，只保留满足条件的组。