MariaDB 教程 / 综合查询练习:月度销售、客户购买与未售商品

同一组订单可以用来统计月度销售额、客户购买额和从未售出的商品,但三份报表的一行代表不同对象。先统一“已付款”和商品金额的定义,再对齐统计期间,才能用总额交叉核对结果;仅仅得到一个看似合理的合计并不足以证明查询正确。

先写清金额、记录粒度与统计期间

本练习的销售额指已付款订单中“数量 × 购买时单价”的合计,不含运费,也不包含已取消订单。这是查询与核对练习,并非完整的收入确认、税务或退款规则。“从未售出”指商品没有出现在任何已付款订单明细中,而不是没有库存、没有订单记录或本月没有销量。

报表 一行代表 本例查询范围
月度销售额 一个年份与月份 2026 年内已付款且有明细的订单
客户购买额 一位客户 全部历史;未购买者保留为零
从未售出的商品 一件已登记商品 全部历史中没有已付款明细

本例所有已付款历史都在 2026 年,因此前两份报表总额相等。实际数据有其他年份时,不能直接比较这两个未经调整的合计;应给客户报表增加同一期间的条件,并保持未购买客户的保留规则。关于分组和连接造成的重复,可结合GROUP BY 与 HAVING 的分组条件和多表连接中的金额重复问题理解。

语法依据是 MariaDB 的 GROUP BY 文档与 EXISTS 子查询文档。先确定报表问题,再选择聚合或存在性判断。

建立可以手工复算的小型订单样本

以 course_user 连接学习服务器,选择 shopcourse。SQL 应在 MariaDB 客户端中执行;也可以按UTF-8 SQL 文件与 SOURCE 执行方法保存文件后,使用 SOURCE C:/mariadb-lab/lesson.sql; 读取实际路径。不要把 SQL 直接输入操作系统的终端提示符。

先执行 SELECT VERSION(), DATABASE(); 和 SHOW TABLES;,确认选中的数据库以及 c42_customers、c42_products、c42_orders、c42_items 是否已经存在。这些名称仅供本篇练习使用;若已存在,先检查结构和数据,不能为重复运行而直接删除应用表。

USE shopcourse;
CREATE TABLE c42_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 c42_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 c42_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_c42_order_customer FOREIGN KEY (customer_id)
  REFERENCES c42_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_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_c42_item_order FOREIGN KEY (order_id)
  REFERENCES c42_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c42_item_product FOREIGN KEY (product_id)
  REFERENCES c42_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c42_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c42_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c42_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 c42_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);

原始 SQL 保留韩文姓名、城市与商品名,以便与其他语言版本得到同样的结果。商品编号 1~4 分别是笔记本、圆珠笔、马克杯和台灯;客户 1、3 的城市值为首尔,客户 2、4 分别为釜山与大田。请保留 UTF-8 字面量,用稳定的编号理解结果,不要只改名称而遗漏关联条件。

应得到四位客户、四件商品、四张订单和六条明细。已付款订单 101 的商品金额为 11500,102 为 8000,103 为 19500,总计 39000;已取消订单 104 的 1500 不计入。这些独立表名也能避免先前事务练习留下的修改影响基线。

按年月汇总,并在订单层级计数

SELECT DATE_FORMAT(o.ordered_at,'%Y-%m') AS month_label,
 COUNT(DISTINCT o.order_id) AS paid_orders,
 SUM(i.quantity*i.unit_price) AS goods_sales
FROM c42_orders o JOIN c42_items i ON i.order_id=o.order_id
WHERE o.status='paid' AND o.ordered_at>='2026-01-01' AND o.ordered_at<'2027-01-01'
GROUP BY DATE_FORMAT(o.ordered_at,'%Y-%m') ORDER BY month_label;
month_label paid_orders goods_sales
2026-01 2 19500.00
2026-02 1 19500.00

连接后的已付款数据有五条明细,却只有三张订单,因此订单数量必须使用 COUNT(DISTINCT o.order_id)。若把它替换为 COUNT(*),一月份会被算成三行而不是两张订单;商品金额仍然需要逐条明细相乘再求和。

使用 %Y-%m 同时保留年份与月份,避免不同年份的一月混在一起。筛选范围包含 2026 年初、不包含 2027 年初。该查询只返回实际存在的月份,不会自动产生三月到十二月的零值行;需要完整月份序列时,应先准备日历集合再与金额结果连接。

这里的订单数实际上是“至少有一条连接成功明细的已付款订单数”。若允许创建没有明细的已付款订单,它不会进入本报表。要统计全部已付款订单,应在订单层级另行计数,并规定无明细订单的处理方式。

按客户汇总,同时保留未购买者

SELECT c.customer_id,c.customer_name,
 COALESCE(SUM(CASE WHEN o.status='paid' THEN i.quantity*i.unit_price ELSE 0 END),0) AS paid_goods
FROM c42_customers c LEFT JOIN c42_orders o ON o.customer_id=c.customer_id
LEFT JOIN c42_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name ORDER BY c.customer_id;

客户编号 1~4 的 paid_goods 应依次是 19500.00、19500.00、0.00、0.00。从客户表开始的 LEFT JOIN 保留客户;CASE 只把已付款明细计入金额,COALESCE 将需要显示的空聚合值变为零。

客户 3 只有已取消订单,客户 4 没有订单,两人都应保留。若在连接之后的 WHERE 中加入 o.status='paid',这两位客户会被移除,而不是显示零。期间限制同样不能随意放到 WHERE;若要与上一份 2026 年报表核对,应在订单的 ON 条件或 CASE 的金额判断中限制该期间,同时维持客户总体。

这份查询只累计商品明细金额。不要直接在这些明细行上累加订单级运费,因为一张订单的运费可能随多条明细重复。增加运费或订单数时,应先按订单预聚合,或另算订单层级指标,再组合到客户层级。

用 NOT EXISTS 查找从未有已付款销售的商品

SELECT p.product_id,p.product_name FROM c42_products p
WHERE NOT EXISTS(SELECT 1 FROM c42_items i JOIN c42_orders o ON o.order_id=i.order_id
 WHERE i.product_id=p.product_id AND o.status='paid') ORDER BY p.product_id;

预期只返回商品 4,即台灯。相关子查询按当前商品编号查找已付款明细;只要存在一条这样的历史记录,NOT EXISTS 就为假。它判断有没有符合条件的行,不关心子查询返回的常量 1 的具体数值。

若一件商品只出现在已取消订单中,按照本篇定义它仍属于“从未售出”。删除子查询中的状态条件,则会变成“从未出现在任何订单明细中”,这是不同问题。若目标是“2026 年没有售出”,还要加入期间条件;不能把全年无销售与整个历史从未销售混用。

逐项核对差额、边界与遗漏记录

初始样本中,两个月的金额之和为 39000,四位客户的金额之和也为 39000。若不一致,按主键、状态、时间范围、购买时单价逐项检查,再检查连接倍增以及 LEFT JOIN 后的筛选。总额相等后仍需检查客户零值、月份缺失与未售商品名单,避免错误相互抵消。

例如增加一笔 2025 年台灯的已付款交易,客户历史金额会增加 25000,台灯也不再属于从未售出的商品,但 2026 年月度金额仍为 39000。这说明对齐观察期间是核对前提。还应规定数据观察时点、DATETIME 所代表的时区以及取消和退款政策;此处固定日期用于重现边界,真实应用应通过参数传递所需期间,不要拼接用户输入。

重复练习前检查数据库、表结构和数据,并备份要保留的内容。以下清理只适用于确认可丢弃的本篇练习表,先删除子表再删除被引用的父表。DROP TABLE 同时移除结构与数据,普通 ROLLBACK 无法撤销,应与事务实验分开执行。

DROP TABLE shopcourse.c42_items;
DROP TABLE shopcourse.c42_orders;
DROP TABLE shopcourse.c42_products;
DROP TABLE shopcourse.c42_customers;