MySQL之case...when...then...end的详细使用

目录

一、简介

  今天我们主要是讲讲case…when…then…end的用法，它主要分成两类：

• 简单Case函数
• Case搜索函数

假设我们数据库有一个员工信息表表如下：

CREATETABLE`tb_employee`(`id`intunsignedNOTNULLAUTO_INCREMENTCOMMENT'主键',`emp_code`intunsignedNOTNULLDEFAULT'0'COMMENT'员工编码',`emp_name`varchar(20)NOTNULLDEFAULT''COMMENT'员工姓名',`gender`char(1)NOTNULLDEFAULT'1'COMMENT'性别(1:男0:女)',`dep_code`intNOTNULLDEFAULT'0'COMMENT'部门',`job`varchar(20)NOTNULLDEFAULT''COMMENT'工作',`age`tinyintNOTNULLDEFAULT'0'COMMENT'年龄',`salary`double(8,2)NOTNULLDEFAULT'0.00'COMMENT'工资',`hire_date`dateDEFAULTNULLCOMMENT'入职时间',`manage_code`intDEFAULTNULLCOMMENT'所属领导',`create_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',`update_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`id`),KEY`idx_emp_code`(`emp_code`),KEY`idx_manage_code`(`manage_code`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COMMENT='员工信息表';

接下来希望你看文章时不要因为sql长而害怕，都是些重复的东西而已，请放宽心态看待。

二、简单Case函数

2.1、语法定义

  语法如下：

CASE'字段名'WHEN'字段值1'THEN'结果1'WHEN'字段值2'THEN'结果2'WHEN'字段值3'THEN'结果3'ELSE'其他结果'END

字段名就是数据库表中字段，字段值就是这个字段存储的值，结果就是你希望得到的结果。

2.2、简单函数形式

  比如我们要查询一份基本的员工信息，数据库里存储的是1或者0，我们肯定不会显示1或者0，而是对应的性别（男或者女），这样更加的直观。从上面tb_student表的定义我们知道字段gender，1表示男，0表示女，默认值是1，这个时候我们就可以利用case…when…then…end来实现

SELECT 
    emp_code AS'员工编号',
    emp_name AS'员工姓名',(CASE gender
        WHEN1THEN'男'WHEN0THEN'女'ELSE'未知'END)AS'性别',
    salary AS'工资'FROM tb_employee;

  还有些人觉得else可以不要，但是不建议这样做，假设数据库没有设置默认值，程序又没有设置值，那就变成空了，又或者有个傻瓜蛋把gender的值改成了2呢？毕竟else是你的一个兜底。尤其是在一些字段可能会扩展的类型的时候，else就显得很重要了。

  一般会把case到end用括号包括，这样也便于解读或者使用别名等。

三、Case搜索函数

3.1、语法定义

  语法如下：

CASEWHEN'表达式1'THEN'结果1'WHEN'表达式2'THEN'结果2'WHEN'表达式3'THEN'结果3'ELSE'其他结果'END

字段名就是数据库表中字段，字段值就是这个字段存储的值，结果就是你希望得到的结果。在Case函数中，表达式可以使用BETWEEN,LIKE,IS NULL,IN,EXISTS等等

3.2、简单用法

  比如我们还是用查询一份基本的员工信息举例看基本使用。

SELECT 
    emp_code AS'员工编号',
    emp_name AS'员工姓名',(CASEWHEN gender=1THEN'男'WHEN gender=0THEN'女'ELSE'未知'END)AS'性别',
    salary AS'工资'FROM tb_employee;

  这样你会发现和上面简单Case函数形式差别很小，确实，如果只是等值表达式，区别很小，并且简单表达式还简单些。这里这么写只是先混个脸熟，根本没有把表达式的作用发挥出来。

3.3、分组

  老板想看看公司里员工的薪资架构是否合理，需要提供一份明细，查询每个人对应的级别，级别规划如下：
工资范围工资级别员工工资小于3000的入门员工工资大于等于3000并且小于15000的初级员工工资大于等于15000并且小于25000的中级员工工资大于等于25000并且小于50000的高级员工工资大于等于50000特级
则我们可以使用case…when…then…end这一语法完成这个查询。

SELECT 
    emp_code AS'员工编号',
    emp_name AS'员工姓名',
    salary AS'员工工资',(CASEWHEN salary <3000THEN'入门级'WHEN salary >=3000AND salary <15000THEN'初级'WHEN salary >=15000AND salary <25000THEN'中级'WHEN salary >=25000AND salary <50000THEN'高级'ELSE'特级'END)AS'工资级别'FROM tb_employee;

  这里的表达式，使用了算术表达式，and表达式，还要between…and 表达式，这里只是告诉大家可以用，实际没必要混着用。

3.4、分组+计数

  老板想看看公司对应的每个工资级别分别有多少人。

SELECT(CASEWHEN salary <3000THEN'入门级'WHEN salary >=3000AND salary <15000THEN'初级'WHEN salary >=15000AND salary <25000THEN'中级'WHEN salary >=25000AND salary <50000THEN'高级'ELSE'特级'END)as'levels',count(*)AS'总人数'FROM tb_employee
GROUPBY levels;

  如果老板还想细分到每个部门，及每个部门对应工资级别的总人数，假设部门编号从10到14分别对应则：
编号部门10总经办11财务12技术13测试14运维
  那么我们只需要先按部门分组，再按工资级别分组即可

SELECT 
    dep_code AS'部门编号',(CASEWHEN dep_code=10THEN'总经办'WHEN dep_code=11THEN'财务'WHEN dep_code=12THEN'技术'WHEN dep_code=13THEN'测试'WHEN dep_code=14THEN'运维'ELSE'其他'END)AS'部门',(CASEWHEN salary <3000THEN'入门级'WHEN salary >=3000AND salary <15000THEN'初级'WHEN salary >=15000AND salary <25000THEN'中级'WHEN salary >=25000AND salary <50000THEN'高级'ELSE'特级'END)AS'levels',count(*)as'总人数'FROM tb_employee
GROUPBY dep_code,levels;

  实际中对应部门名称肯定是以连表查询居多，我这里是为了演示，顺便加深case…when…then…end用法的使用

3.5、分组+汇总

  如果老板现在想知道，每个部门的总工资，及每个部门中每个工资级别每个月总工资是多少。小伙伴们想到的可能是先按部门分组，再按性别分组，然后再汇总。如果是一条记录显示这个结果，我相信很多小伙伴也不知道怎么去查询。

  我们不着急，我们先查个简单的，查询每个部门的男生总数和女生总数，以及部门的总人数。那么case…when…then…end的作用又来了。

SELECT 
    dep_code AS'部门编号',(CASEWHEN dep_code=10THEN'总经办'WHEN dep_code=11THEN'财务'WHEN dep_code=12THEN'技术'WHEN dep_code=13THEN'测试'WHEN dep_code=14THEN'运维'ELSE'其他'END)AS'部门',SUM((CASEWHEN gender =1THEN1ELSE0END))AS'男生人数',SUM((CASEWHEN gender =0THEN1ELSE0END))AS'女生人数',COUNT(*)AS'部门总人数'FROM
    tb_employee
GROUPBY dep_code;

  也许即算看了代码，也许还是有不理解的，为什么两个总数在一行。

• count(*)按部门分组，同一个部门的每一条记录都会加入结果集
• case…when…then…end这个是同一个部门中，只有满足条件才会记录到结果集，我们这里满足就记为1，不满足，记为0，然后使用sum函数汇总

  了解了上面这个后，我们之前那个需求每个部门的总工资，及每个部门中每个工资级别每个月总工资是多少就容易理解了，查询如下：

SELECT 
    dep_code AS'部门编号',(CASEWHEN dep_code=10THEN'总经办'WHEN dep_code=11THEN'财务'WHEN dep_code=12THEN'技术'WHEN dep_code=13THEN'测试'WHEN dep_code=14THEN'运维'ELSE'其他'END)AS'部门',SUM(salary)AS'总工资',SUM((CASEWHEN salary <=3000THEN salary ELSE0END))AS'入门总工资',SUM((CASEWHEN salary >3000AND salary <15000THEN salary ELSE0END))AS'初级总工资',SUM((CASEWHEN salary >=15000AND salary <25000THEN salary ELSE0END))AS'中级总工资',SUM((CASEWHEN salary >=25000AND salary <=50000THEN salary ELSE0END))AS'高级总工资',SUM((CASEWHEN salary >50000THEN salary ELSE0END))AS'特级总工资'FROM
    tb_employee
GROUPBY dep_code;

  其实还算可以更详细每个部门的总人数，总工资，及每个部门中每个工资级别的人数及每个级别对应的总工资是多少

SELECT 
    dep_code AS'部门编号',(CASEWHEN dep_code=10THEN'总经办'WHEN dep_code=11THEN'财务'WHEN dep_code=12THEN'技术'WHEN dep_code=13THEN'测试'WHEN dep_code=14THEN'运维'ELSE'其他'END)AS'部门',COUNT(*)AS'总人数',SUM(salary)AS'总工资',SUM((CASEWHEN salary <=3000THEN1ELSE0END))AS'入门总人数',SUM((CASEWHEN salary >3000AND salary <15000THEN1ELSE0END))AS'初级总人数',SUM((CASEWHEN salary >=15000AND salary <25000THEN1ELSE0END))AS'中级总人数',SUM((CASEWHEN salary >=25000AND salary <=50000THEN1ELSE0END))AS'高级总人数',SUM((CASEWHEN salary >50000THEN1ELSE0END))AS'特级总人数',SUM((CASEWHEN salary <=3000THEN salary ELSE0END))AS'入门总工资',SUM((CASEWHEN salary >3000AND salary <15000THEN salary ELSE0END))AS'初级总工资',SUM((CASEWHEN salary >=15000AND salary <25000THEN salary ELSE0END))AS'中级总工资',SUM((CASEWHEN salary >=25000AND salary <=50000THEN salary ELSE0END))AS'高级总工资',SUM((CASEWHEN salary >50000THEN salary ELSE0END))AS'特级总工资'FROM
    tb_employee
GROUPBY dep_code;

  相当于两个例子合并了，还可以计算平均工资等就不一一列举了。

3.6、更新语句

  公司部门编号从10到20，公司对员工的工资进行调整，除去部门10以外
工资范围工资级别员工工资小于3000的涨薪400员工工资大于等于3000并且小于15000的涨薪20%员工工资大于等于15000并且小于25000的涨薪10%员工工资大于等于25000并且小于50000的不变员工工资大于等于50000降薪10%

UPDATE tb_employee 
SET 
    salary =(CASEWHEN salary <=3000THEN salary +400WHEN salary >3000AND salary <=15000THEN salary *1.2WHEN salary >15000AND salary <25000THEN salary *1.1WHEN salary >50000THEN salary *0.9ELSE salary
    END)where dep_code >10;

3.7、子查询

  比如对账时有本地记录tb_local_record和外部记录tb_outside_record，通过查询看哪些本地记录没有对应的外部记录。

SELECT 
    tranSeq as'交易流水',(CASEWHEN tranSeq IN(SELECT tranSeq FROM tb_outside_record)THEN'匹配'ELSE'未匹配'END)as'是否匹配'FROM tb_local_record;

或者

SELECT 
    lr.tranSeq as'交易流水',(CASEWHENEXISTS(SELECT osr.tranSeq FROM tb_outside_record osr 
                    WHERE  osr.tranSeq = lr.tranSeq)THEN'匹配'ELSE'未匹配'END)as'是否匹配'FROM tb_local_record lr;

结语

  case…when…then…end的用法还有很多，比如还能联合count函数，但是一般有以上的方式，基本上就够你工作所需了。