SQLite GROUP BY 与 HAVING：过滤聚合结果

GROUP BY 把行归入分组

COUNT、SUM 和 AVG 这样的聚合函数把许多行归纳成一个数字。GROUP BY 让你按类别做这件事：每个客户一个数字、每个月一个数字、每种状态一个数字。每个不同的值（或值的组合）在结果中变成一行。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   50.00),
    ('Ada',   30.00),
    ('Boris', 80.00),
    ('Boris', 20.00),
    ('Boris', 15.00),
    ('Cleo', 200.00);

SELECT customer, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer;

三个客户，输出三行。原来的六行不见了：它们被归入了按客户划分的分组，COUNT(*) 和 SUM(amount) 在每个分组内分别计算。

可以这样理解：GROUP BY customer 的意思是“把客户相同的所有行当作一组”。然后聚合函数对每一组分别运算。

SELECT 列表里能写什么

这一点常常让人犯错。使用 GROUP BY 时，SELECT 列表中的每一列，要么出现在 GROUP BY 子句中，要么放在聚合函数里。否则这个值就是不明确的：它应该取自分组中的哪一行？

CREATE TABLE sales (
    region TEXT,
    rep TEXT,
    amount REAL
);

INSERT INTO sales VALUES
    ('North', 'Ada', 100),
    ('North', 'Boris', 200),
    ('South', 'Cleo', 150);

-- This works: region is grouped, amount is aggregated.
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

如果你写了 SELECT region, rep, SUM(amount) 并配上 GROUP BY region，SQLite 会照常执行（其他数据库会拒绝，而 SQLite 比较宽松），但 rep 会从分组中任意挑一个。每个地区你会得到一个销售代表的名字，但无法保证是哪一个。不要依赖这种行为：显示的每一个非聚合列都要放进 GROUP BY。

HAVING 在聚合之后过滤分组

WHERE 在分组之前过滤行，HAVING 在分组之后过滤分组。区别就这么简单，这也是为什么你不能把 COUNT(*) > 1 写进 WHERE 子句：WHERE 执行的时候，计数还不存在。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada', 50), ('Ada', 30),
    ('Boris', 80), ('Boris', 20), ('Boris', 15),
    ('Cleo', 200);

SELECT customer, COUNT(*) AS order_count
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1;

Cleo 只下过一个订单，所以她的分组被过滤掉了。剩下 Ada 和 Boris。条件针对的是每个分组的聚合值，而不是单独的行。

你可以在 HAVING 中直接引用 SELECT 列表里的列别名，SQLite 允许这样做：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   50.00),
    ('Ada',   30.00),
    ('Boris', 80.00),
    ('Boris', 20.00),
    ('Boris', 15.00),
    ('Cleo', 200.00);

SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING total >= 100;

这通常比在 HAVING 子句中重复写 SUM(amount) 更易读。

WHERE 与 HAVING：一起使用

这两个子句不是二选一。WHERE 决定哪些行参与分组，HAVING 决定哪些分组进入输出。大多数实际查询两者都会用到。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada', 50, 'paid'),
    ('Ada', 30, 'refunded'),
    ('Boris', 80, 'paid'),
    ('Boris', 20, 'paid'),
    ('Cleo', 200, 'paid'),
    ('Cleo', 50, 'refunded');

SELECT customer, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 75;

按执行顺序从上往下读：

1. WHERE status = 'paid'：彻底去掉已退款的行。
2. GROUP BY customer：把剩下的行按客户分组。
3. SUM(amount) 在每个分组内运行。
4. HAVING SUM(amount) > 75：只保留满足条件的分组。

Boris（80 + 20 = 100）和 Cleo（200）留了下来。Ada 唯一已付款的订单是 50，没有达到门槛。

多个条件与多个分组列

HAVING 接受与 WHERE 相同的布尔运算符：AND、OR、NOT。你还可以按多列分组，得到更细的子分组：

CREATE TABLE sales (
    region TEXT,
    quarter TEXT,
    amount REAL
);

INSERT INTO sales VALUES
    ('North', 'Q1', 100), ('North', 'Q1', 50),
    ('North', 'Q2', 300),
    ('South', 'Q1', 80),
    ('South', 'Q2', 120), ('South', 'Q2', 60);

SELECT region, quarter, SUM(amount) AS total, COUNT(*) AS deals
FROM sales
GROUP BY region, quarter
HAVING SUM(amount) > 100 AND COUNT(*) >= 2;

每一对 (region, quarter) 都是一个单独的分组。HAVING 子句要求总额超过 100，并且至少有两笔交易。只有 ('North', 'Q1') 和 ('South', 'Q2') 符合条件。

实用模式：查找重复值

GROUP BY ... HAVING COUNT(*) > 1 查询是查找某一列中重复值的标准方法：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO users (email) VALUES
    ('ada@example.com'),
    ('boris@example.com'),
    ('ada@example.com'),
    ('cleo@example.com'),
    ('boris@example.com');

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

找出了两个重复值。接下来你通常要决定是合并账号、添加 UNIQUE 约束，还是清理数据，但用来发现问题的查询每次都是同样的结构。

不带 GROUP BY 的 HAVING

这种写法不常见，但合法。没有 GROUP BY 时，整个结果集被当作一个分组，HAVING 把它作为整体来过滤：要么得到全部聚合值，要么什么也得不到：

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (50), (30), (80);

SELECT COUNT(*) AS total_orders, SUM(amount) AS revenue
FROM orders
HAVING SUM(amount) > 100;

之所以出现这一行结果，是因为总和是 160。把门槛改成 > 200，查询就一行也不返回。实际中，你几乎总会把 HAVING 和 GROUP BY 搭配使用，但知道语言本身并不强制这样做也有好处。

快速回顾

• GROUP BY 把行按键归入分组，聚合函数在每个分组内运行。
• SELECT 中的每个非聚合列都应该出现在 GROUP BY 中。
• WHERE 在分组之前过滤行，HAVING 在分组之后过滤分组。
• COUNT(*) 和 SUM(...) 这样的聚合函数属于 HAVING，绝不能放在 WHERE 中。
• HAVING 接受复合条件，并且可以引用 SELECT 中的别名。

下一篇：外键

对单张表做聚合很有用，但大多数真实的表结构会把数据分散到多张表中：订单在这里，客户在那里，商品又在别处。外键就是把这些表连接起来、让它们之间的关系保持一致的方法。这就是下一章的内容。