MySQL 分组依据和跳过对空值的分组

在使用MySQL进行数据分析和查询时，我们经常需要对数据进行分组操作。MySQL的分组功能允许我们按照某个字段的值将数据分为多个组，并对每个组进行聚合计算。然而，在进行分组操作时，我们有时会遇到一些空值的情况，这就需要我们跳过对空值的分组。下面我们将详细介绍MySQL的分组依据和跳过对空值的分组。

sql
SELECT age, COUNT(*) FROM students GROUP BY age;

sql
SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id HAVING customer_id IS NOT NULL;

sql
-- 创建学生表
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  score INT
);
-- 插入测试数据
INSERT INTO students (id, name, age, score) VALUES (1, '张三', 18, 90);
INSERT INTO students (id, name, age, score) VALUES (2, '李四', 19, 80);
INSERT INTO students (id, name, age, score) VALUES (3, '王五', NULL, 70);
INSERT INTO students (id, name, age, score) VALUES (4, '赵六', 20, 85);
-- 按照年龄进行分组
SELECT age, COUNT(*) FROM students GROUP BY age;
-- 创建订单表
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10, 2)
);
-- 插入测试数据
INSERT INTO orders (order_id, customer_id, amount) VALUES (1, 1, 100);
INSERT INTO orders (order_id, customer_id, amount) VALUES (2, NULL, 200);
INSERT INTO orders (order_id, customer_id, amount) VALUES (3, 2, 150);
INSERT INTO orders (order_id, customer_id, amount) VALUES (4, 3, 180);
-- 按照客户ID进行分组，并计算订单总金额
SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id HAVING customer_id IS NOT NULL;