MySQL 分组后统计记录条数（GROUP BY 与 COUNT() 详解）

SELECT class, COUNT(*) AS total
FROM students
GROUP BY class;

-- 创建学生表
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    class VARCHAR(20) NOT NULL,  -- 班级
    gender ENUM('male', 'female') NULL,  -- 性别（允许NULL）
    score INT NULL  -- 分数（允许NULL）
);
 
-- 插入测试数据
INSERT INTO students (name, class, gender, score) VALUES
('Alice', 'Class A', 'female', 85),
('Bob', 'Class A', 'male', 90),
('Charlie', 'Class B', 'male', 88),
('David', 'Class B', 'male', 92),
('Eve', 'Class A', 'female', 87),
('Frank', 'Class C', 'male', 78),
('Grace', 'Class C', NULL, NULL),  -- 性别和分数为NULL
('Henry', 'Class C', 'male', 80);

SELECT 
    class, 
    COUNT(*) AS total_students  -- 统计组内所有记录
FROM students
GROUP BY class;

SELECT 
    class, 
    COUNT(score) AS students_with_score  -- 仅统计score不为NULL的记录
FROM students
GROUP BY class;

SELECT 
    class, 
    COUNT(DISTINCT gender) AS distinct_genders  -- 统计去重后的性别数
FROM students
GROUP BY class;

SELECT 
    class, 
    gender, 
    COUNT(*) AS student_count
FROM students
GROUP BY class, gender;  -- 多字段分组，先按class分，再按gender分

-- 订单表结构
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_time DATETIME,
    amount DECIMAL(10,2)
);
 
-- 插入测试数据
INSERT INTO orders (user_id, order_time, amount) VALUES
(1, '2023-01-01 10:00:00', 100.00),
(1, '2023-01-02 14:00:00', 150.00),
(2, '2023-01-01 09:30:00', 200.00),
(3, '2023-01-03 16:00:00', 50.00),
(3, '2023-01-03 17:00:00', 80.00);
 
-- 统计SQL
SELECT 
    user_id, 
    COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- 日志表结构
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_time DATETIME,
    log_level VARCHAR(10),
    message TEXT
);
 
-- 按日期分组统计（提取日期部分）
SELECT 
    DATE(log_time) AS log_date,  -- 提取日期
    COUNT(*) AS log_count
FROM logs
GROUP BY DATE(log_time);  -- 按日期分组

-- 商品表结构
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,  -- 分类ID
    product_name VARCHAR(50),
    sku VARCHAR(20)  -- 库存单位（可能重复）
);
 
-- 统计每个分类的不同SKU数
SELECT 
    category_id, 
    COUNT(DISTINCT sku) AS unique_sku_count
FROM products
GROUP BY category_id;

-- 给students表的class字段加索引
CREATE INDEX idx_class ON students(class);
 
-- 给orders表的user_id字段加索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 错误：字段是INT，查询用字符串
SELECT user_id, COUNT(*) FROM orders WHERE user_id = '1' GROUP BY user_id;
 
-- 正确：保持类型一致
SELECT user_id, COUNT(*) FROM orders WHERE user_id = 1 GROUP BY user_id;

-- 分批统计（按ID范围）
SELECT class, COUNT(*) AS cnt FROM students WHERE id BETWEEN 1 AND 1000 GROUP BY class;
SELECT class, COUNT(*) AS cnt FROM students WHERE id BETWEEN 1001 AND 2000 GROUP BY class;
-- 合并结果（业务层处理或用UNION ALL+SUM）

EXPLAIN SELECT class, COUNT(*) FROM students GROUP BY class;

-- 临时调大（仅当前会话生效）
SET SESSION tmp_table_size = 1024 * 1024 * 100;  -- 100M
SET SESSION max_heap_table_size = 1024 * 1024 * 100;

-- 复合索引：包含分组字段和统计字段
CREATE INDEX idx_class_score ON students(class, score);
 
-- 统计时，只查询索引字段
SELECT class, COUNT(score) FROM students GROUP BY class;

SELECT gender, COUNT(*) FROM students GROUP BY gender;

-- 错误：不支持多字段DISTINCT
SELECT class, COUNT(DISTINCT gender, score) FROM students GROUP BY class;
 
-- 正确：用CONCAT拼接（注意分隔符避免歧义）
SELECT class, COUNT(DISTINCT CONCAT(gender, '_', score)) FROM students GROUP BY class;

CONCAT(COALESCE(gender, 'null'), '_', COALESCE(score, 'null'))

-- 正确：HAVING筛选组
SELECT class, COUNT(*) AS cnt 
FROM students 
GROUP BY class 
HAVING cnt > 2;
 
-- 错误：WHERE不能筛选组（会先过滤掉class的记录，导致分组错误）
SELECT class, COUNT(*) AS cnt 
FROM students 
WHERE cnt > 2  -- 错误，cnt是聚合结果，但WHERE在分组前执行，无法识别
GROUP BY class;