MySQL中CASE函数的应用：将查询列作为判断条件

MySQL中CASE函数的应用：将查询列作为判断条件

引用

CSDN

1.

https://blog.csdn.net/z_xiao_qiang/article/details/113584576

在数据库查询中，有时需要根据列的值来判断并进行相应的统计或计算。MySQL的CASE函数提供了一种强大的解决方案，可以将查询的列作为判断条件，实现复杂的查询需求。本文通过一个具体的订单统计场景，详细讲解了CASE函数的使用方法，并给出了多个实际应用示例。

场景描述

假设我们有一个订单表order，结构如下：

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mch_id` varchar(255) DEFAULT NULL COMMENT '商户id',
  `device_info` varchar(255) DEFAULT NULL COMMENT '设备号',
  `mch_name` varchar(255) DEFAULT NULL COMMENT '商户名称',
  `status` varchar(255) DEFAULT NULL COMMENT '状态 0-成功 2-退款 3- 失败',
  `amount` varchar(255) DEFAULT NULL COMMENT '交易金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

表中插入了一些示例数据：

INSERT INTO `xiaoqiang_test`.`order`(`id`, `mch_id`, `device_info`, `mch_name`, `status`, `amount`) VALUES (1, '1', '101', '超市', '0', '1');
INSERT INTO `xiaoqiang_test`.`order`(`id`, `mch_id`, `device_info`, `mch_name`, `status`, `amount`) VALUES (2, '2', '102', '百货', '2', '2');
INSERT INTO `xiaoqiang_test`.`order`(`id`, `mch_id`, `device_info`, `mch_name`, `status`, `amount`) VALUES (3, '3', '103', '菜市场', '3', '3');

查询需求

我们需要查询某日的总交易金额、失败金额和退款金额。

传统方法

使用传统方法，我们需要分别执行以下三条SQL语句：

1. 总交易金额查询：

select sum(amount) from `order`;

2. 失败金额查询：

select sum(amount) from `order` where status = '3';

3. 退款金额查询：

select sum(amount) from `order` where status = '2';

这种方法需要执行多条SQL语句，效率较低。接下来，我们介绍如何使用CASE函数来优化这个查询。

使用CASE函数优化查询

MySQL的CASE函数允许我们将查询的列作为判断条件，从而实现更复杂的查询需求。CASE函数的基本语法如下：

• sum(case 属性名 when 属性值1 then 1 else 0 end): 当某个属性下为属性值1就加1个数量，否则就作0统计。
• sum(case when 条件 then 指定列 else 0 end): 当when后面的条件成立,就将指定列进行求和，否则就作0统计。

使用CASE函数，我们可以将上述三个查询合并为一个：

select 
sum(amount) AS '总交易金额',
sum(case when status = '2' then amount ELSE 0 END) AS '退款金额',
sum(case when status = '3' then amount ELSE 0 END) AS '失败金额'
from `order`;

这样，我们就可以通过一条SQL语句获取所有需要的数据。

CASE函数的其他应用场景

除了上述示例，CASE函数还有其他多种应用场景：

简单CASE函数

case `gender`
when 1 then '男'
when 2 then '女'
else '未知'
end

CASE搜索函数

case 
when gender = 1 then '男'
when gender = 2 then '女'
else '未知' 
end

通过这些示例，我们可以看到CASE函数在实际项目中的广泛应用。它不仅可以用于数值计算，还可以用于字符串转换等场景，极大地提高了SQL查询的灵活性和效率。