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;




