问小白 wenxiaobai
资讯
历史
科技
环境与自然
成长
游戏
财经
文学与艺术
美食
健康
家居
文化
情感
汽车
三农
军事
旅行
运动
教育
生活
星座命理

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`;

  1. 失败金额查询:
select sum(amount) from `order` where status = '3';

  1. 退款金额查询:
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查询的灵活性和效率。

© 2023 北京元石科技有限公司 ◎ 京公网安备 11010802042949号