MariaDB 教程 / 用 CASE 选择条件值并进行条件聚合
CASE 是根据条件选择结果值的 SQL 表达式,可以生成状态标签或用于条件聚合。编写时应明确分支顺序、ELSE 的默认结果与 NULL 规则,因为这些都会改变每行的结果以及最后的汇总。
区分查询表达式与程序控制语句
本节 CASE 放在 SELECT 中,为当前行计算一个值。它与存储程序中控制多条 SQL 执行的 CASE 语句不同,不是在这里依次执行一组更新操作。搜索式 CASE 选择第一个为真的条件;多个范围重叠时,前后顺序就是规则的一部分。
| 形式 | 用途 | 关键点 |
|---|---|---|
| CASE 值 WHEN 比较值 THEN 结果 | 把相等的代码映射为结果 | 逐项比较给定值 |
| CASE WHEN 条件 THEN 结果 | 处理范围或复合条件 | 第一个为真的条件决定结果 |
| ELSE 结果 | 没有匹配项时的默认值 | 省略且未匹配时会得到 NULL |
| END | 结束表达式 | 后面可以接结果列别名 |
语法可查阅 MariaDB 官方的 CASE 表达式文档。把 CASE 放入聚合函数时,还需要理解 COUNT 与 SUM 的不同规则;条件写对,不代表计数单位和业务指标也一定正确。
准备不同库存状态的完整商品表
以 course_user 连接学习服务器,先检查 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;。下面使用独立表 products_24_lab;同名对象已存在时,先查看定义和记录,确认用途,不要直接覆盖。
在 MariaDB 客户端中执行完整代码,或者保存为 UTF-8 文件,再按照 SQL 文件与 SOURCE 教程使用实际路径加载。SQL 不应直接输入操作系统终端。原始韩文字符串保持不变,方便跨语言核对同一份 UTF-8 数据。
USE shopcourse;
CREATE TABLE products_24_lab (
product_id INT NOT NULL AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
note VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (product_id),
CONSTRAINT ck_p24_price CHECK (price >= 0),
CONSTRAINT ck_p24_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_24_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
(2, '볼펜', '문구', 1500.00, 0, ''),
(3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
(4, '스탠드', '생활', 25000.00, 5, NULL),
(5, '파일', '문구', 1500.00, 50, ''),
(6, '케이블', '전자', 8000.00, 0, '호환 확인');
编号 1~6 分别表示笔记本、圆珠笔、马克杯、台灯、文件夹和线缆,分类 문구、생활、전자 分别表示文具、生活用品与电子产品。两件商品库存为零,一件在 1~9 之间,三件至少为 10。
这些只是练习用的库存标签。实际销售状态可能还受预留数量、在途补货、商品是否停用等条件影响,不应把这个简单 CASE 当成完整的可售判断规则。
让较窄的重叠条件先得到判断
SELECT product_id,product_name,stock,
CASE WHEN stock=0 THEN '품절'
WHEN stock<10 THEN '재고 적음'
ELSE '판매 가능' END AS stock_label
FROM products_24_lab ORDER BY product_id;
编号 2、6 得到 품절,意思是“缺货”;编号 4 得到 재고 적음,意思是“库存较少”;编号 1、3、5 得到 판매 가능,意思是“可售”。SQL 中标签保留原文,核对时用编号与库存值对应。
库存为零同时满足 stock=0 和 stock<10。如果先判断小于 10,零库存会先进入“库存较少”,后面的缺货分支就无法纠正。可以只交换这两个 WHEN 的顺序,观察编号 2、6 的标签如何变化,再恢复原顺序,确认标签恢复。
没有 ELSE 且所有条件都不匹配时,结果为 NULL。例如一个始终为假的条件不会自动返回零。出现空白结果时,应分别检查输入缺失与分支未覆盖;两者可能都导致 NULL,但需要采取的处理不同。
比较条件 SUM 与条件 COUNT 的结果
SELECT SUM(CASE WHEN stock=0 THEN 1 ELSE 0 END) AS out_of_stock,
COUNT(CASE WHEN stock=0 THEN 1 END) AS also_out_of_stock,
COUNT(CASE WHEN stock=0 THEN 1 ELSE 0 END) AS all_non_null,
SUM(CASE WHEN category='문구' THEN price*stock ELSE 0 END) AS stationery_value
FROM products_24_lab;
| 表达式模式 | 预期结果 | 原因 |
|---|---|---|
| SUM:满足时 1,否则 0 | 2 | 每条零库存记录贡献一个 1 |
| COUNT:满足时 1,否则 NULL | 2 | 只统计非 NULL 的表达式结果 |
| COUNT:满足时 1,否则 0 | 6 | 零也是非 NULL,六行全部被计入 |
| 文具分类的库存金额 | 145000.00 | 仅对该分类逐行计算价格乘库存,再相加 |
COUNT 并不是只数值为 1 的记录。第三种写法中,无论条件真假,CASE 都返回非 NULL 的数,所以总数为六。要统计满足条件的行,应有意识地选择 SUM 的 1/0 模式,或 COUNT 的 1/NULL 模式,而不是随意在两者之间替换。
最后一个表达式把文具商品的 price*stock 计入金额,其他分类贡献零。这里汇总的是金额,不是商品件数;条件、返回值和聚合函数应共同对应需要的单位。
如果输入本身完全没有行,以上两个 SUM 的结果为 NULL,两个 COUNT 的结果为 0。CASE 中写了 ELSE 0,并不意味着空输入时一定会产生一行零供 SUM 使用。需要显示默认总额时,应单独决定空集的处理规则。
保持分支类型明确,并保留需要的零值组
不要在同一个金额表达式的不同分支中任意混入文字标签与数字。需要后续计算或数值排序时,应让金额保持适当的数值类型,另外生成显示标签。缺失价格是否应该当作零,也应先确定业务含义,不能仅为让结果不空就随意填补。
SELECT category,
SUM(CASE WHEN stock>0 THEN 1 ELSE 0 END) AS available_products
FROM products_24_lab GROUP BY category ORDER BY category;
这条查询按分类返回有库存的商品数:文具 2、生活用品 2、电子产品 0。电子产品组仍然存在,因为编号 6 参与了分组,只是在 CASE 中贡献零。
若先加 WHERE stock>0,编号 6 会在分组前被排除,电子产品组就完全消失。报表需要“列出存在的分类,并显示零”时,条件聚合与预过滤不是同一种结果形状。这个差异可以对照 GROUP BY、WHERE 与 HAVING 教程理解。
注意这种写法保留的是输入中原本存在的分类。如果某分类在商品表中一行也没有,仅靠这里的 CASE 不会凭空创建该组;要展示完整分类目录,需要另有分类来源与明确的连接规则。
先检查每行分支,再信任汇总
聚合总数有疑问时,先显示主键、库存与 CASE 输出,核对每行落入哪个分支。依次检查等号与范围端点、AND/OR 的括号、重叠条件顺序,以及省略 ELSE 是否符合预期。随后把逐行结果相加,再与总额比较。
本节所有 CASE 都在 SELECT 中计算,不需要改变保存的 stock。若观察分支顺序后结果不恢复,先核对实际查询与原始六条记录;不要通过改写库存去迎合预期标签。
练习结束后可保留表供检查。若要重建,先确认数据库、表定义与记录,并备份需要保留的数据。下面只删除已确认可丢弃的本节表;DROP TABLE 会移除定义和全部行,普通 ROLLBACK 无法恢复,应与事务实验分开。
DROP TABLE shopcourse.products_24_lab;




