Mysql把查询的列作为判断条件(case函数)

最新推荐文章于 2024-07-03 03:09:02 发布

原创最新推荐文章于 2024-07-03 03:09:02 发布 · 8.7k 阅读

5 ·

CC 4.0 BY-SA版权

本文介绍MySQL中Case函数的使用方法及应用场景，通过实例演示如何利用Case函数简化查询操作，实现一条SQL语句获取多种状态的汇总信息。

1.遇到了一个查询统计的场景，从而引出思考？
mysql将查询的列作为判断条件。

表结构
在这里插入图片描述

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');

一.场景如下：
查询出某日的总交易金额,失败金额，退款金额;

总交易金额查询:

 select sum(amount) from `order` ;

在这里插入图片描述

失败金额查询:

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

在这里插入图片描述

退款金额查询:

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

在这里插入图片描述
此场景需要多条sql查询，因为查询的条件status状态有三种，成功-退款-失败，所以如果在where后面跟条件，需要写三条查询，由此引发思考，如何合并查询，把条件写到需要查询的字段中，可以实现一条sql查询不同订单状态：
mysql的case函数：
在这里插入图片描述
这是官方的描述；
简单举个例子理解：

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

即:

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_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

搜索函数

 CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

三.使用示例

简单case函数

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

case搜索函数

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