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