MariaDB 教程 / 用 WITH 和公共表表达式拆分查询

公共表表达式(Common Table Expression,CTE)通过 WITH 为一条 SQL 中的中间查询结果命名。它能把复杂条件和聚合分成便于阅读的步骤,但不会创建永久表,也不保证执行一定更快。

让每个中间结果只承担一个明确任务

把已支付订单筛选、商品金额汇总和客户姓名连接全部塞进一层嵌套表达式,容易看不清条件在哪一步生效。本例用 paid_orders 表示符合条件的订单,用 order_totals 表示每单商品金额;每个名称都应有清楚的列、键与行含义。

结果名称 一行代表什么 主要列
paid_orders 一笔已支付订单 order_id、customer_id
order_totals 一笔订单的明细金额合计 order_id、goods_total
最终 SELECT 一笔符合条件且有明细合计的订单 订单编号、客户姓名、商品金额

本节使用非递归 CTE,不让定义引用自身。具体语法见 MariaDB 官方的 WITH 文档,命名中间结果的用法可参阅 非递归 CTE 说明。

准备独立的完整订单样本

以 course_user 连接学习服务器,先检查 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;。确认版本支持这里的 WITH 写法,并检查 c31_customers、c31_products、c31_orders、c31_items 是否已经存在;若同名对象存在,先查看定义与记录,不要覆盖用途不明的数据。

完整准备代码应在 MariaDB 客户端中执行,也可按照 SQL 文件与 SOURCE 教程保存 UTF-8 文件并提供实际路径。以下四表和数据都独立提供,不需要沿用上一节的表。

USE shopcourse;
CREATE TABLE c31_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c31_order_customer FOREIGN KEY (customer_id)
  REFERENCES c31_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c31_item_order FOREIGN KEY (order_id)
  REFERENCES c31_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c31_item_product FOREIGN KEY (product_id)
  REFERENCES c31_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c31_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c31_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c31_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c31_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

姓名、城市和商品名保留原始韩文字符串。商品 1~4 为笔记本、圆珠笔、马克杯和台灯;客户 1、3 的城市表示首尔,2 表示釜山,4 表示大田。用编号核对结果,保持输入的 UTF-8 字面量不变。

四个订单中有三个 paid、一个 canceled。先把明细按订单汇总,可以更容易检查 JOIN 后金额重复相加的问题。拆分查询不是为了改变报表含义,而是让原来的筛选范围与统计单位可以逐步核对。

在最终 SELECT 前定义多个 CTE

WITH paid_orders AS (
 SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
), order_totals AS (
 SELECT order_id,SUM(quantity*unit_price) AS goods_total
 FROM c31_items GROUP BY order_id
)
SELECT p.order_id,c.customer_name,t.goods_total
FROM paid_orders p JOIN order_totals t ON t.order_id=p.order_id
JOIN c31_customers c ON c.customer_id=p.customer_id
ORDER BY p.order_id;
订单编号 客户编号 商品金额
101 1 11500.00
102 1 8000.00
103 2 19500.00

实际查询显示客户姓名,上表用编号解释归属。客户 1 的两笔订单仍分别占一行;给中间结果起了名称,不会自动把它们合成客户级汇总。商品总金额为 39000,取消订单不进入最终结果。

整条语句开头只写一次 WITH,各个命名定义之间用逗号分隔,内部 SELECT 放在 AS 后的括号中。最后一个定义后直接接最终 SELECT,而不是再写逗号。分号放在整个 SQL 的末尾;在中间定义后提前写分号,会把语句切断。

每个中间结果都只输出后面需要的明确列。order_totals 按 order_id 聚合,确保每单一个合计,最终连接才不会因为明细条数而扩成多行。

先检查中间记录,再核对最终金额

如果最终结果不同,先单独执行每个 CTE 括号内的 SELECT。paid_orders 应有 101、102、103。order_totals 没有状态筛选,因此可先包含四笔订单,104 的商品金额为 1500;最终连接到 paid_orders 时,取消订单才被排除。

WITH paid_orders AS (
 SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
)
SELECT * FROM paid_orders ORDER BY order_id;

这条较小的语句保留 paid_orders 定义,把最后的 SELECT 改为直接查看中间结果。应看到订单与客户编号 101→1、102→1、103→2。只把状态改为 canceled 时,应得到 104→3;比较后恢复 paid,再确认前三笔订单。

排查时要说明取消条件在哪一步生效。中间结果多一笔取消订单不一定是错误,只要最终逻辑明确排除它;反过来,过早排除记录也可能让后续对照无法完成。应按每一步的职责判断,而不是要求所有中间结果都长得像最后的报表。

若要改为每位客户一行,需要另行按客户聚合,不能仅把 CTE 改成一个听起来像客户汇总的名称。字段和行的实际计算方式才决定粒度。

理解单条语句的作用域与优化器选择

CTE 名称只在所属 SQL 中有效。前一条 WITH 查询结束后,单独执行 SELECT * FROM paid_orders,不会继续访问刚才的结果;本例数据库没有同名表时会提示表不存在。再次在新语句中写出 WITH 定义,名称才重新可用。

它不是会自动保留到下一个请求的临时数据表,也没有一个永久 CTE 对象需要 DROP。若定义必须供多个语句使用,可以评估视图;若要把中间行实际保存并在多个步骤读取,则应评估适当的临时表设计,不能混淆三者的生命周期。

优化器可能根据查询与版本合并或物化中间结构。WITH 不承诺只执行一次,也不承诺一定快于等价嵌套查询。可读性与性能应分别评估;需要性能判断时,使用 EXPLAIN 和代表性数据检查实际计划,不要从名字或排版推断执行方式。

本例最终使用 INNER JOIN,所以 paid 订单若没有明细合计,就不会进入结果。如果报表要求连这类订单也保留,应明确使用 LEFT JOIN,并决定缺失合计是否显示为零。相关人群区别见 LEFT JOIN 与过滤位置教程。

检查名称、输出列与分组单位

语法错误先看括号、逗号与分号;列不存在时,检查该列是否真的由对应 CTE 的 SELECT 输出。同一 WITH 中的名称应唯一,避免使用遮蔽基础表含义的名字。普通非递归定义引用前面已经定义的 CTE 时,也应让依赖顺序清楚。

结果金额异常时,逐步读出主键与合计,检查同一订单是否被重复连接。拆成多个名称不能自动修正错误关系,仍应像普通 JOIN 一样核对每个阶段的数量与单位。

本节 SELECT 不改写四张源表,也没有 CTE 对象要清理。只在确认练习表可以丢弃后,备份所需数据并执行下面的子表优先清理。DROP TABLE 会删除定义和全部记录,普通 ROLLBACK 无法恢复,应与事务实验分开。

DROP TABLE shopcourse.c31_items;
DROP TABLE shopcourse.c31_orders;
DROP TABLE shopcourse.c31_products;
DROP TABLE shopcourse.c31_customers;