MySQL分组

概述

GROUP BY子句将具有相同值的行分组为汇总行。它通常与COUNT、SUM、AVG、MIN、MAX等聚合函数一起使用以生成分组汇总。

GROUP BY语法

基本分组

单列分组

-- Group by single column

-- Group by country

-- Count per category

多列分组

-- Group by multiple columns

-- Detailed grouping

聚合函数

计数

-- Count all rows

-- Count non-NULL values

-- Count distinct values

-- Count per group

求和

-- Sum of values

-- Sum per group

-- Sum with condition

平均值

-- Average value

-- Average per group

-- Round average

最小值和最大值

-- Minimum value

-- Maximum value

-- First and last date

HAVING子句

基本HAVING

-- Filter groups with HAVING

-- Having with aggregate

HAVING与WHERE比较

方面	WHERE	HAVING
Filters	Rows before grouping	Groups after grouping
With GROUP BY	Before group	After group
Aggregate functions	Cannot use	Can use
Performance	Faster	Slower

-- WHERE filters rows before grouping

-- HAVING filters groups after grouping

复杂HAVING

-- Multiple conditions

-- With OR

带JOIN的GROUP BY

-- Group with JOIN

-- Complex JOIN with GROUP BY

汇总

基本汇总

-- Generate subtotals and grand total

-- Result includes:
-- - Details by category and subcategory
-- - Subtotals by category
-- - Grand total

GROUPING()函数

-- Identify NULL from ROLLUP

-- 1 = generated by ROLLUP (supertotal row)
-- 0 = normal grouping row

立方体

CUBE语法

-- Generate all combinations

-- Creates summaries for:
-- - All combinations of category and subcategory
-- - Each category total
-- - Each subcategory total
-- - Grand total

分组集

基本分组集

-- Specific groupings

等效的ROLLUP和CUBE

-- ROLLUP equivalent
-- Equivalent to:

-- CUBE equivalent
-- Equivalent to:

GROUP BY与DISTINCT

-- Count distinct values per group

-- Complex distinct count

实用示例

销售报表

-- Daily sales summary

用户统计

-- Users by activity level

产品分析

-- Products by category with stats

性能考虑

索引使用

-- Index on GROUP BY columns helps

-- Index for WHERE + GROUP BY

优化技巧

-- Filter before grouping

-- Use appropriate data types
-- Numeric grouping is faster than string grouping

-- Avoid SELECT * with GROUP BY
-- Select only necessary columns

故障排除

常见问题

-- Column not in GROUP BY or aggregate
-- Error: Expression #X of SELECT list is not in GROUP BY
-- Solution: Add column to GROUP BY or use aggregate

-- Unexpected results
-- Check: GROUP BY columns, aggregate functions
-- Verify: Filter conditions in WHERE vs HAVING

调试

-- Check group formation

-- Verify aggregate values

小结

GROUP BY子句提供：

分组：按列值对行分组 聚合：COUNT、SUM、AVG、MIN、MAX HAVING：分组后过滤组 高级分组：ROLLUP、CUBE、分组集 性能：在分组列上使用索引 用例：报表、分析、汇总

---

上一个：ORDER BY

下一个：JOINs