SQLite Having 子句

SQLite 的 HAVING 子句 用于在 GROUP BY 分组后，过滤聚合结果。它是“分组后的 WHERE”，只能与 GROUP BY 一起使用，且条件中通常包含聚合函数（COUNT、SUM、AVG 等）。

---

基本语法

SELECT column, aggregate_function(...)
FROM table_name
[WHERE condition]           -- 过滤原始行
GROUP BY column
HAVING aggregate_condition  -- 过滤分组结果
[ORDER BY ...]
[LIMIT ...];

子句	作用对象	典型条件
WHERE	原始行	status = 'active'
HAVING	分组后的结果	COUNT(*) > 5

---

示例表结构

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product TEXT,
    category TEXT,
    amount REAL,
    sale_date DATE
);

INSERT INTO sales (product, category, amount, sale_date) VALUES
('Laptop Pro',  'Electronics', 1200, '2025-11-01'),
('T-Shirt',     'Clothing',     35, '2025-11-01'),
('Mouse',       'Electronics',  25, '2025-11-02'),
('Jeans',       'Clothing',     80, '2025-11-02'),
('Keyboard',    'Electronics',  90, '2025-11-03'),
('Coffee Mug',  'Home',         15, '2025-11-03'),
('Monitor',     'Electronics', 350, '2025-11-04'),
('Sneakers',    'Clothing',    120, '2025-11-05');

---

1. 基础 HAVING：筛选高销量类别

-- 查找销售数量 ≥ 3 的商品类别
SELECT 
    category,
    COUNT(*) AS item_count,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING COUNT(*) >= 3;

结果：

category     | item_count | total_sales
-------------|------------|------------
Electronics  | 4          | 1665.0

---

2. HAVING vs WHERE 对比

-- 错误：WHERE 不能用于聚合函数
-- SELECT category, COUNT(*) FROM sales WHERE COUNT(*) > 2 GROUP BY category;

-- 正确：用 HAVING
SELECT category, COUNT(*) 
FROM sales 
GROUP BY category 
HAVING COUNT(*) > 2;

-- WHERE 先过滤行，再分组
SELECT category, SUM(amount)
FROM sales
WHERE amount > 50
GROUP BY category;

---

3. 复杂 HAVING 条件

-- 总销售额 > 500 且 平均单价 > 100 的类别
SELECT 
    category,
    COUNT(*) AS count,
    SUM(amount) AS total,
    AVG(amount) AS avg_price
FROM sales
GROUP BY category
HAVING SUM(amount) > 500 AND AVG(amount) > 100;

---

4. 结合时间函数分组 + HAVING

-- 每月销售额 > 1000 的月份
SELECT 
    STRFTIME('%Y-%m', sale_date) AS month,
    SUM(amount) AS monthly_sales
FROM sales
GROUP BY month
HAVING monthly_sales > 1000;

---

5. 多表 + HAVING（JOIN 示例）

-- 假设有 customers 表
SELECT 
    c.name,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN sales o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING total_spent > 1000 OR order_count >= 5;

---

6. HAVING + 表达式 / CASE

-- 高价值订单（>300）占比 > 50% 的类别
SELECT 
    category,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN amount > 300 THEN 1 ELSE 0 END) AS high_value_orders
FROM sales
GROUP BY category
HAVING 
    CAST(SUM(CASE WHEN amount > 300 THEN 1 ELSE 0 END) AS REAL) 
    / COUNT(*) > 0.5;

---

7. 性能优化

-- 为 GROUP BY 和 HAVING 常用列建索引
CREATE INDEX idx_category_amount ON sales(category, amount);
CREATE INDEX idx_sale_date ON sales(sale_date);

HAVING 条件若能提前用 WHERE1 过滤，建议移到 WHERE 提高性能。

---

8. 常见错误

错误	正确做法
WHERE COUNT(*) > 2	改为 HAVING COUNT(*) > 2
HAVING category = 'IT'	应提前用 WHERE category = 'IT'
忘记 GROUP BY	HAVING 必须配合 GROUP BY

---

快速参考

需求	SQL
订单数 > 5	HAVING COUNT(*) > 5
总金额 < 1000	HAVING SUM(amount) < 1000
平均 > 100 且 数量 ≥ 2	HAVING AVG(price) > 100 AND COUNT(*) >= 2
高价值订单占比 > 30%	HAVING SUM(CASE WHEN ... THEN 1 ELSE 0 END)/COUNT(*) > 0.3

---

最佳实践

1. WHERE 过滤行 → GROUP BY 分组 → HAVING 过滤组
2. 能用 WHERE 的条件不要放 HAVING
3. 为 GROUP BY 列建复合索引
4. 复杂逻辑用 CASE + 聚合

---

需要我帮你写一个针对你表的 HAVING 统计吗？
请提供表名、分组字段和筛选条件！