MySQL（八）子查询与分组查询：从基础到进阶的实战指南

在MySQL的查询体系中，子查询（Subquery）和分组查询（Grouped Query）是处理复杂数据逻辑的两大核心工具。子查询通过嵌套查询实现"先算后用"的逻辑，而分组查询则通过GROUP BY对数据进行聚合分析（如求和、计数、平均值）。两者的结合能解决诸如"找出销量Top10的产品"、"计算用户的平均订单金额并筛选高价值用户"等实际业务问题。

本文将从基础概念、实战案例、最佳实践到常见陷阱，全面拆解子查询与分组查询的使用技巧。我们会基于一个模拟的电商数据库（用户、订单、商品、订单详情表）展开讲解，所有示例均贴合真实业务场景。

目录#

  1. 前置知识：示例数据库设计
  2. 子查询：嵌套逻辑的艺术
    • 2.1 子查询的定义与分类
    • 2.2 非关联子查询（Non-Correlated Subquery）
    • 2.3 关联子查询（Correlated Subquery）
    • 2.4 子查询的位置：SELECT/FROM/WHERE
  3. 分组查询：聚合分析的核心
    • 3.1 GROUP BY的基础用法
    • 3.2 聚合函数：SUM/COUNT/AVG/MAX/MIN
    • 3.3 HAVING：过滤聚合结果
    • 3.4 ROLLUP：生成小计与总计
  4. 子查询与分组查询的结合：复杂分析的利器
  5. 最佳实践：性能与可读性双提升
  6. 常见陷阱：避坑指南
  7. 总结
  8. 参考资料

1. 前置知识：示例数据库设计#

为了让示例更贴近真实场景，我们定义以下4张表（字段均含注释）：

1.1 用户表（users）#

存储用户基本信息：

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT, -- 用户ID（主键）
  name VARCHAR(50) NOT NULL,              -- 用户名
  register_date DATE NOT NULL             -- 注册日期
);

1.2 订单表（orders）#

存储订单核心信息：

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT, -- 订单ID（主键）
  user_id INT NOT NULL,                     -- 用户ID（外键关联users.user_id）
  order_date DATETIME NOT NULL,             -- 下单时间
  total_amount DECIMAL(10,2) NOT NULL       -- 订单总金额
);

1.3 商品表（products）#

存储商品信息：

CREATE TABLE products (
  product_id INT PRIMARY KEY AUTO_INCREMENT, -- 商品ID（主键）
  name VARCHAR(100) NOT NULL,                -- 商品名称
  category VARCHAR(50) NOT NULL,             -- 商品分类（如"电子设备"）
  price DECIMAL(10,2) NOT NULL               -- 商品原价
);

1.4 订单详情表（order_items）#

存储订单中的商品明细：

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY AUTO_INCREMENT, -- 订单详情ID（主键）
  order_id INT NOT NULL,                        -- 订单ID（外键关联orders.order_id）
  product_id INT NOT NULL,                      -- 商品ID（外键关联products.product_id）
  quantity INT NOT NULL DEFAULT 1,              -- 购买数量
  unit_price DECIMAL(10,2) NOT NULL             -- 实际成交单价（可能含折扣）
);

2. 子查询：嵌套逻辑的艺术#

2.1 子查询的定义与分类#

子查询是嵌套在另一个查询中的SELECT语句，它的结果会作为外层查询的输入。根据子查询与外层查询的依赖关系，可分为两类：

  • 非关联子查询：子查询的执行不依赖外层查询的结果（独立运行）；
  • 关联子查询：子查询的执行依赖外层查询的每行数据（需逐行匹配）。

2.2 非关联子查询：独立运行的嵌套#

非关联子查询是最基础的形式，常用于筛选符合条件的行（如"找出下过单的用户"）。

示例1：找出所有下过单的用户#

需求：从users表中筛选出至少有1条订单记录的用户。

SELECT user_id, name 
FROM users 
WHERE user_id IN (
  SELECT user_id FROM orders -- 子查询：获取所有有订单的用户ID
);

逻辑解析：

  1. 先执行子查询SELECT user_id FROM orders，得到所有下单用户的ID列表；
  2. 外层查询用IN判断用户ID是否在列表中。

2.3 关联子查询：逐行匹配的依赖#

关联子查询的关键是子查询中引用了外层查询的字段（如o.user_id），因此它会外层查询的每一行运行一次。常用于行级对比（如"找出超过用户平均订单金额的订单"）。

示例2：找出超过用户平均订单金额的订单#

需求：从orders表中筛选出"订单金额大于该用户历史平均订单金额"的订单。

SELECT order_id, user_id, total_amount 
FROM orders o -- 外层查询别名o
WHERE total_amount > (
  -- 关联子查询：计算当前用户（o.user_id）的平均订单金额
  SELECT AVG(total_amount) 
  FROM orders 
  WHERE user_id = o.user_id -- 引用外层查询的o.user_id
);

逻辑解析：

  1. 外层查询遍历orders表的每一行（记为o）；
  2. 子查询针对当前行的user_id，计算该用户的平均订单金额；
  3. 外层查询判断当前订单金额是否大于该平均值。

2.4 子查询的位置：SELECT/FROM/WHERE#

子查询可以出现在外层查询的3个位置，对应不同的使用场景：

场景1：子查询在WHERE中（最常用）#

用于筛选行，如示例1、示例2。

场景2：子查询在SELECT中（标量子查询）#

用于为每行添加计算字段（如"查询用户的订单数量"）。子查询必须返回单个值（ scalar，一行一列），否则会报错。

示例3：查询用户的订单数量#

需求：从users表中获取用户名称及对应的订单数（无订单则显示0）。

SELECT 
  name,
  -- 标量子查询：计算当前用户的订单数（COUNT(*)返回0而非NULL）
  (SELECT COUNT(*) FROM orders WHERE user_id = u.user_id) AS order_count 
FROM users u;

注意：若子查询返回NULL（如MAX(order_date)），可通过COALESCE函数设置默认值：

SELECT 
  name,
  COALESCE(
    (SELECT MAX(order_date) FROM orders WHERE user_id = u.user_id),
    '1970-01-01' -- 无订单时的默认日期
  ) AS last_order_date 
FROM users u;

场景3：子查询在FROM中（派生表）#

子查询的结果会被当作临时表（称为"派生表"，Derived Table），外层查询可对其进行再查询。常用于多步聚合（如"先算用户平均订单金额，再筛选高于全局平均的用户"）。

示例4：筛选平均订单金额高于全局平均的用户#

需求：先计算每个用户的平均订单金额，再筛选出"平均金额高于平台全局平均"的用户。

SELECT user_id, avg_order 
FROM (
  -- 子查询（派生表）：计算每个用户的平均订单金额
  SELECT user_id, AVG(total_amount) AS avg_order 
  FROM orders 
  GROUP BY user_id
) AS user_avg -- 派生表必须指定别名
WHERE avg_order > (
  -- 子查询：计算平台全局平均订单金额
  SELECT AVG(total_amount) FROM orders
);

逻辑解析：

  1. 内层子查询（派生表user_avg）先按用户分组，计算每个用户的平均订单金额；
  2. 外层查询用WHERE筛选出平均金额高于全局平均的用户。

3. 分组查询：聚合分析的核心#

分组查询的核心是GROUP BY clause，它将数据按指定字段分组，再通过聚合函数（Aggregate Function）计算每组的统计值。常见的聚合函数有：

  • SUM()：求和（如总销量）；
  • COUNT()：计数（如订单数）；
  • AVG()：平均值（如平均客单价）；
  • MAX()/MIN()：最大值/最小值（如最新订单时间）。

3.1 GROUP BY的基础用法#

GROUP BY的语法格式：

SELECT 分组字段, 聚合函数(字段)
FROM 表名
[JOIN 关联表 ON 关联条件]
WHERE 行过滤条件
GROUP BY 分组字段;

示例5：计算每个商品的总销量#

需求：从order_items和products表中，统计每个商品的总销量（数量×单价）。

SELECT 
  p.product_id, -- 分组字段（商品ID）
  p.name AS product_name, -- 商品名称（需在GROUP BY中或为聚合字段）
  SUM(oi.quantity * oi.unit_price) AS total_sales -- 总销量（聚合计算）
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id -- 关联商品表
GROUP BY p.product_id, p.name; -- 分组字段（product_id是主键，name依赖于它）

关键说明：

  • MySQL 5.7+的严格模式：若SELECT中包含非聚合字段（如p.name），则必须将其加入GROUP BY（或依赖于分组字段的主键/唯一键）。这是因为only_full_group_by是默认的SQL模式，避免返回不确定的结果。

3.2 HAVING：过滤聚合结果#

WHERE用于分组前过滤行（如筛选"电子设备"分类的商品），而HAVING用于分组后过滤组（如筛选"总销量>1000元"的商品）。

示例6：筛选总销量超过1000元的商品#

需求：在示例5的基础上，只保留总销量大于1000元的商品。

SELECT 
  p.product_id,
  p.name AS product_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE p.category = '电子设备' -- 分组前过滤：只看电子设备分类
GROUP BY p.product_id, p.name
HAVING total_sales > 1000; -- 分组后过滤：总销量>1000元

逻辑差异：

  • WHERE p.category = '电子设备'：先过滤出电子设备的订单详情，再分组；
  • HAVING total_sales > 1000：先分组计算总销量，再过滤出符合条件的商品。

3.3 ROLLUP：生成小计与总计#

WITH ROLLUP是GROUP BY的扩展，用于生成分组的小计和总计（如"每个分类的销量+所有分类的总销量"）。

示例7：计算商品分类的销量小计与总计#

需求：统计每个商品分类的总销量，并生成所有分类的总销量。

SELECT 
  p.category AS product_category, -- 分类（分组字段）
  SUM(oi.quantity * oi.unit_price) AS total_sales -- 总销量
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category WITH ROLLUP; -- 生成小计与总计

结果示例：

product_categorytotal_sales
电子设备5000.00
服装3000.00
NULL8000.00

4. 子查询与分组查询的结合：复杂分析的利器#

子查询与分组查询的结合能解决多步分析问题。以下是两个典型场景：

场景1：用分组子查询筛选高销量商品#

需求：找出"被下单次数超过5次"的商品（同一订单中多次购买同一商品算1次）。

SELECT p.name AS product_name 
FROM products p 
WHERE EXISTS (
  -- 子查询（分组）：计算该商品的订单数（去重）
  SELECT 1 -- 用1代替具体字段，性能更优
  FROM order_items oi 
  WHERE oi.product_id = p.product_id 
  GROUP BY oi.product_id 
  HAVING COUNT(DISTINCT oi.order_id) > 5 -- 订单数>5
);

逻辑解析：

  1. 外层查询遍历products表的每一行；
  2. 子查询针对当前商品，计算其被下单的** distinct 订单数**（同一订单多次购买算1次）；
  3. 用EXISTS判断是否存在满足条件的订单数。

场景2：用派生表计算用户的累计消费#

需求：先计算每个用户的累计消费金额，再筛选出"累计消费>500元"的用户。

SELECT 
  u.user_id,
  u.name AS user_name,
  user_total.total_amount AS total_consumption -- 累计消费
FROM users u
JOIN (
  -- 子查询（派生表）：计算每个用户的累计消费
  SELECT user_id, SUM(total_amount) AS total_amount 
  FROM orders 
  GROUP BY user_id
) AS user_total ON u.user_id = user_total.user_id
WHERE user_total.total_amount > 500; -- 筛选累计消费>500元的用户

5. 最佳实践：性能与可读性双提升#

5.1 子查询的最佳实践#

  • 优先用EXISTS代替IN：当子查询返回大量结果时，EXISTS的性能更优（它只需判断"是否存在"，而非遍历整个列表）。例如： 
    -- 推荐：用EXISTS代替IN
SELECT user_id, name FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);
 
-- 不推荐：IN在子查询结果大时较慢
SELECT user_id, name FROM users WHERE user_id IN (SELECT user_id FROM orders);
  • 避免不必要的关联子查询：关联子查询会逐行运行，若外层表数据量大，性能会很差。尽量用JOIN代替，例如： 
    -- 原关联子查询（慢）
SELECT o.* FROM orders o 
WHERE total_amount > (SELECT AVG(total_amount) FROM orders WHERE user_id = o.user_id);
 
-- 优化：用JOIN+分组子查询（快）
SELECT o.* 
FROM orders o
JOIN (
  SELECT user_id, AVG(total_amount) AS avg_amount 
  FROM orders 
  GROUP BY user_id
) AS user_avg ON o.user_id = user_avg.user_id
WHERE o.total_amount > user_avg.avg_amount;
  • 用CTE代替派生表（MySQL 8.0+）：WITH clause（CTE，Common Table Expression）能将复杂的派生表拆分为多个可读性更强的临时表。例如示例4可改写为： 
    WITH 
-- 定义CTE：user_avg（每个用户的平均订单金额）
user_avg AS (
  SELECT user_id, AVG(total_amount) AS avg_order FROM orders GROUP BY user_id
),
-- 定义CTE：global_avg（全局平均订单金额）
global_avg AS (
  SELECT AVG(total_amount) AS avg_total FROM orders
)
-- 主查询：筛选符合条件的用户
SELECT user_id, avg_order 
FROM user_avg 
JOIN global_avg ON user_avg.avg_order > global_avg.avg_total;

5.2 分组查询的最佳实践#

  • 严格遵循only_full_group_by模式：MySQL 5.7+默认开启only_full_group_by，要求SELECT中的非聚合字段必须在GROUP BY中。避免模糊查询（如SELECT * FROM ... GROUP BY ...），明确指定字段。
  • WHERE先过滤，HAVING后过滤：WHERE过滤行的成本低于HAVING（HAVING需先分组再过滤）。例如，若要筛选"电子设备"分类的商品，应在WHERE中处理，而非HAVING： 
    -- 推荐：WHERE先过滤分类
SELECT p.name, SUM(oi.quantity) FROM order_items oi 
JOIN products p ON oi.product_id = p.product_id 
WHERE p.category = '电子设备' -- 先过滤分类
GROUP BY p.name;
 
-- 不推荐：HAVING后过滤（效率低）
SELECT p.name, SUM(oi.quantity) FROM order_items oi 
JOIN products p ON oi.product_id = p.product_id 
GROUP BY p.name 
HAVING p.category = '电子设备';
  • 避免过度分组：仅按需要的字段分组（如按product_id分组即可，无需同时按product_id和name，除非name不是product_id的依赖字段）。

6. 常见陷阱：避坑指南#

陷阱1：忘记在GROUP BY中包含非聚合字段#

错误示例：

-- 错误：name未在GROUP BY中，且未被聚合（MySQL 5.7+报错）
SELECT p.name, SUM(oi.quantity) 
FROM order_items oi 
JOIN products p ON oi.product_id = p.product_id 
GROUP BY p.product_id;

原因：only_full_group_by模式要求SELECT中的非聚合字段必须在GROUP BY中。若product_id是products表的主键，name依赖于product_id，则可改为：

-- 正确：GROUP BY包含product_id（主键），name可省略（MySQL会自动推导）
SELECT p.product_id, p.name, SUM(oi.quantity) 
FROM order_items oi 
JOIN products p ON oi.product_id = p.product_id 
GROUP BY p.product_id;

陷阱2：用WHERE过滤聚合结果#

错误示例：

-- 错误：WHERE不能过滤聚合函数（SUM(total_sales)）
SELECT p.name, SUM(oi.quantity * oi.unit_price) AS total_sales 
FROM order_items oi 
JOIN products p ON oi.product_id = p.product_id 
WHERE total_sales > 1000 -- WHERE无法处理聚合结果
GROUP BY p.name;

原因：WHERE是"行级过滤"，执行于分组之前，无法识别聚合函数的结果。应改用HAVING：

-- 正确：用HAVING过滤聚合结果
SELECT p.name, SUM(oi.quantity * oi.unit_price) AS total_sales 
FROM order_items oi 
JOIN products p ON oi.product_id = p.product_id 
GROUP BY p.name 
HAVING total_sales > 1000;

陷阱3：关联子查询的性能问题#

错误场景：当外层表有10万行时，关联子查询会运行10万次，导致查询超时。 解决：用JOIN+分组子查询代替关联子查询（见5.1节的优化示例）。

陷阱4：混淆COUNT(*)与COUNT(字段)#

  • COUNT(*)：统计所有行（包括NULL值）；
  • COUNT(字段)：统计字段非NULL的行。 示例：统计用户的订单数（包括NULL，即无订单的用户）：
-- 正确：COUNT(*)统计所有行（无订单时返回0）
SELECT u.name, COUNT(o.order_id) AS order_count 
FROM users u 
LEFT JOIN orders o ON u.user_id = o.user_id 
GROUP BY u.user_id;
 
-- 错误：COUNT(o.order_id)会忽略NULL（无订单时返回0，结果相同？不，LEFT JOIN后无订单的用户order_id为NULL，COUNT(o.order_id)返回0，与COUNT(*)结果一致？其实这里两者结果相同，但COUNT(*)更高效）

7. 总结#

子查询与分组查询是MySQL中处理复杂业务逻辑的核心工具：

  • 子查询：通过嵌套实现"先算后用"，适用于行级筛选、字段扩展；
  • 分组查询：通过GROUP BY和聚合函数实现数据聚合，适用于统计分析；
  • 结合使用：能解决多步分析问题（如高价值用户筛选、商品销量TopN）。

掌握它们的关键是理解逻辑顺序（子查询的执行时机、GROUP BY的分组逻辑）和遵循最佳实践（避免关联子查询、严格分组字段、用CTE提升可读性）。

8. 参考资料#

  1. MySQL官方文档：子查询（Subqueries）
    https://dev.mysql.com/doc/refman/8.0/en/subqueries.html
  2. MySQL官方文档：分组查询（GROUP BY）
    https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html
  3. MySQL官方文档：SQL模式（sql_mode）
    https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by
  4. 《高性能MySQL》（第3版）：第6章 优化查询
  5. MySQL 8.0新特性：CTE（WITH clause）
    https://dev.mysql.com/doc/refman/8.0/en/with.html
2026-05