分组聚合

GROUP BY 与 HAVING

GROUP BY 把数据按指定列分组，配合聚合函数（SUM、COUNT、AVG 等）计算每组的统计值。

HAVING 对分组后的结果做过滤，和 WHERE 的区别是：WHERE 在分组前过滤行，HAVING 在分组后过滤组。

SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id
HAVING total_sales > 1000;

SQL 执行顺序

理解执行顺序能帮你判断"这个条件该写在 WHERE 还是 HAVING"：

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

所以 WHERE 里不能用聚合函数（还没分组），HAVING 里可以。SELECT 里的别名在 HAVING 中能否使用取决于数据库——MySQL 允许，但标准 SQL 不允许。

实战：每月销量冠军

典型的分组聚合 + 窗口函数组合拳：

WITH monthly_sales AS (
    SELECT product_id,
        DATE_FORMAT(sale_date, '%Y-%m') AS month,
        SUM(amount) AS total_sales
    FROM sales
    GROUP BY product_id, month
),
ranked AS (
    SELECT *,
        RANK() OVER(PARTITION BY month ORDER BY total_sales DESC) AS rnk
    FROM monthly_sales
)
SELECT product_id, month, total_sales
FROM ranked
WHERE rnk = 1;

先用 GROUP BY 聚合每产品每月销量，再用 RANK() 排名取第一。这种 CTE + 窗口函数的套路在面试中出现频率非常高。

练习

SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
ORDER BY total DESC
LIMIT 3;

WITH monthly AS (
  SELECT product_id,
    DATE_FORMAT(sale_date, '%Y-%m') AS month,
    SUM(amount) AS total
  FROM sales
  GROUP BY product_id, DATE_FORMAT(sale_date, '%Y-%m')
),
with_prev AS (
  SELECT *,
    LAG(total) OVER(PARTITION BY product_id ORDER BY month) AS prev_total,
    LAG(month) OVER(PARTITION BY product_id ORDER BY month) AS prev_month
  FROM monthly
)
SELECT DISTINCT product_id
FROM with_prev
WHERE total > 500 AND prev_total > 500;