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

布满荆棘的人生 2023-10-05 01:56 9阅读 0赞

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

表结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw_size_16_color_FFFFFF_t_70]

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

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

1.  总交易金额查询:

select sum(amount) from `order` ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw_size_16_color_FFFFFF_t_70 1]

*  失败金额查询:

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw_size_16_color_FFFFFF_t_70 2]

*  退款金额查询:

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

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw_size_16_color_FFFFFF_t_70 5]  
这就完成了,合并多条sql语句；  
当然，真实场景比上面的例子复杂多，只是抛砖引玉；  
二.case的其它的应用场景：

1.  简单函数

CASE case_value
        WHEN when_value THEN statement_list
        [WHEN when_value THEN statement_list] ...
        [ELSE statement_list]
    END CASE

1.  搜索函数

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

三.使用示例

1.  简单case函数

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

1.  case搜索函数

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210203094615305.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210203095914453.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210203114621723.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210203114745741.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2021020314011569.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20210203143656169.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfeGlhb19xaWFuZw==,size_16,color_FFFFFF,t_70