怎么用ANSI SQL做分组

在 ANSI SQL 里，“分组”主要用 GROUP BY 子句来实现，用来把多行数据按某些列“分堆”，再对每一堆做聚合计算。

下面按从基础到进阶给你讲清楚。

一、最基本的分组（GROUP BY）

示例表

orders
+----------+------------+--------+
| order_id | customer_id| amount |
+----------+------------+--------+
| 1        | 101        | 200    |
| 2        | 101        | 300    |
| 3        | 102        | 150    |
+----------+------------+--------+

按 customer_id 分组，统计每个客户的订单数

SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

✅ 结果：

customer_id | order_count
------------+------------
101         | 2
102         | 1

二、常见聚合函数（ANSI SQL 标准）

示例：每个客户的总金额

SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

三、分组后过滤（HAVING）

WHERE 过滤“分组前”
HAVING 过滤“分组后”

只保留总金额大于 400 的客户

SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 400;

四、多列分组

SELECT
    customer_id,
    YEAR(order_date) AS order_year,
    COUNT(*) AS cnt
FROM orders
GROUP BY customer_id, YEAR(order_date);

⚠️ 注意：
SELECT 里出现的非聚合列，必须出现在 GROUP BY 中（ANSI SQL 严格要求）。

五、GROUP BY + ORDER BY

SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY total_amount DESC;

六、GROUP BY 的执行顺序（很重要）

FROM
→ WHERE
→ GROUP BY
→ HAVING
→ SELECT
→ ORDER BY

七、常见错误示例 ❌

❌ 错误：SELECT 中包含未分组列

SELECT
    customer_id,
    order_id,   -- ❌ 不在 GROUP BY 中
    SUM(amount)
FROM orders
GROUP BY customer_id;

✅ 正确做法：

• 把 order_id 加入 GROUP BY
• 或对 order_id 使用聚合函数

八、进阶：GROUPING SETS / ROLLUP / CUBE（ANSI SQL）

1️⃣ ROLLUP（层级汇总）

SELECT
    customer_id,
    YEAR(order_date),
    SUM(amount)
FROM orders
GROUP BY ROLLUP(customer_id, YEAR(order_date));

2️⃣ GROUPING SETS（自定义分组）

SELECT
    customer_id,
    YEAR(order_date),
    SUM(amount)
FROM orders
GROUP BY GROUPING SETS (
    (customer_id, YEAR(order_date)),
    (customer_id),
    ()
);

九、一句话总结

ANSI SQL 分组 = GROUP BY + 聚合函数 + HAVING

如果你愿意，可以告诉我：

• 你用的是 MySQL / PostgreSQL / Oracle / SQL Server
• 还是在做 面试题 / 报表 / 数据分析

我可以给你更贴近实际场景的写法。