case...when...

1 语法

Case具有两种格式:
简单Case函数和Case搜索函数。

• 简单Case函数

CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

• Case搜索函数

CASE 
WHEN sex = '1' THEN '男' 
WHEN sex = '2' THEN '女' 
ELSE '其他'  END  

简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式

2 案例

2.1 案例1

首先我们来创建一个比赛获胜表，表里有年份，获胜情况

create  table `test`.`com_score`(
`id` int(11) not null auto_increment comment'主键',
`year` varchar(10)  default '0' comment '年份',
`win_loss` varchar(10)  not null ,
primary key(`id`),
key `index_name`(`year`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='比赛得分表';

数据库名、表名、字段名  要用   `  `     ,其他的要用 ' '
注意不要用符合与内容之间不要有空格  比如`id`   'id' 

然后来插入一些数据

insert into `com_score`(`year`,`win_loss`)
values
('2005','负'),
('2005','平'),
('2005','胜'),
('2005','平'),
('2005','胜'),
('2005','平'),
('2005','胜'),
('2005','负'),
('2005','胜');

注意：  数据库名、表名、字段名  要用   `  `       其他的要用 ' '
       要注意涉及的符号 括号等都是英文状态下，若出现报错，要检查这些地方是否有错误

报错信息：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '（'2005','负'),
('2005','平'),
('2005','胜'),
('2005','平'),
('2005','' at line 3

这种情况下就是括号出现了错误

下面我们来进行case when
1）将 win_loss 中的胜，负，平 都变成 对应的 ‘win’,‘loss’,‘tie’

select year,
    case 
    when win_loss='胜' then 'win'
    when win_loss='负' then 'loss' 
    else 'tie' 
    end win_loss
    from com_score;

2. 假设胜得3分，平得一分，负得0分，统计每一年的得分

select year ,
sum(case when win_loss='胜' then 3 when win_loss='平' then 1 else 0 end ) score 
from com_score group by year;

3）统计每一年的 胜场数，平场数 和 负场数

• 明细

select year ,
case when win_loss='胜' then 3 
      when win_loss='负' then 2 
      when win_loss='平' then 1 else 0 end  
from com_score;

注意这两个的区别
select year ,
case when win_loss='胜' then 1 else 0 ,
case when win_loss='负' then 1 else 0 ,
case when win_loss='平' then 1 else 0 end  
from com_score;

• 汇总

select year ,
sum(case when win_loss='胜' then 1 else 0 end ) '胜场数' ,
sum(case when win_loss='负' then 1 else 0 end) '负场数',
sum(case when win_loss='平' then 1 else 0 end) '平场数' 
from com_score group by year;

2.2 案例2

新建一张表；

create table `test`.`stu_score`(
`id` int(11) not null auto_increment comment '主键',
`name` VARCHAR(20) DEFAULT '0' comment '名字',
`score` int(10)  DEFAULT null COMMENT'分数',
PRIMARY key(`id`),
key `name`(`name`)
) ENGINE=INNODB auto_increment=1 DEFAULT CHARSET=utf8mb4 COMMENT='分数表';

ops,忘记建了一列，我们把它加上去

alter table `stu_score` add `subject` varchar(20) DEFAULT null;

接着，我们插入数据

insert into `stu_score`(`name`,`subject`,`score`)
VALUES
('zhangsan','语文','80'),
('zhangsan','数学','98'),
('zhangsan','英语','64'),
('lishi','语文','70'),
('lishi','数学','88'),
('lishi','英语','60');

比较下面的不同

select name, 
(case when subject='语文' then score else 0 end) '语文', 
(case when subject ='数学' then score else 0 end) '数学', 
(case when subject ='英语' then score else 0 end) '英语'
from stu_score;


select name, 
(case when subject='语文' then score else 0 end) '语文', 
(case when subject ='数学' then score else 0 end) '数学', 
(case when subject ='英语' then score else 0 end) '英语'
from stu_score group by name;
问题原因：分组要有聚合函数搭配；当明细和聚合函数放在一起时，系统会自动选取返回的第一条记录，其他符合条件的被舍弃

比较下面两个的不同

select name, 
(case when subject='语文' then score else 0 end) '语文', 
(case when subject ='数学' then score else 0 end) '数学', 
(case when subject ='英语' then score else 0 end) '英语'
from stu_score;


select name, 
max(case  when subject='语文' then score else 0 end) '语文', 
max(case when subject ='数学' then score else 0 end) '数学', 
max(case when subject ='英语' then score else 0 end) '英语'
from stu_score group by name;
这里是选取了

2）统计学生文科，理科的总分。

select name as '姓名',
       case
        when subject='数学' then '理科' else '文科' end as '科别',score,sum(score) as '总分'
    from stu_score
		group by name,
             case 
             when subject='数学' then '理科' else '文科' end ; 

2.3 案例3

• 已知数据按照另外一种方式进行分组，分析。

CREATE table `c_p`(
`id` int(11) not null auto_increment,
`country` varchar(20) DEFAULT null,
`population` int(20) DEFAULT null,
PRIMARY key (`id`),
key `country`(`country`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

insert into `c_p`(`country`,`population`)
values
('中国',600),
('美国',100),
('加拿大',100),
('英国',200),
('法国',300),
('日本',250),
('德国',200),
('墨西哥',50),
('印度',250);

统计亚洲和北美洲的人数

SELECT  SUM(population), 
CASE country  WHEN '中国' THEN '亚洲' 
  WHEN '印度' THEN '亚洲' 
  WHEN '日本' THEN '亚洲' 
  WHEN '美国' THEN '北美洲' 
  WHEN '加拿大'  THEN '北美洲' 
  WHEN '墨西哥'  THEN '北美洲' 
ELSE '其他' END 
FROM    c_p 
GROUP BY
CASE country 
WHEN '中国' THEN '亚洲' 
WHEN '印度' THEN '亚洲'
WHEN '日本' THEN '亚洲' 
WHEN '美国' THEN '北美洲' 
WHEN '加拿大'  THEN '北美洲' 
WHEN '墨西哥'  THEN '北美洲' 
ELSE '其他' END;

• 用一个SQL语句完成不同条件的分组

CREATE table `c_s_p`(
`id` int(11) not null auto_increment,
`country` varchar(20) DEFAULT null,
`sex` varchar(10) DEFAULT null,
`population` int(20) DEFAULT null,
PRIMARY key (`id`),
key `country`(`country`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

insert into `c_s_p`(`country`,`sex`,`population`)
values
('中国',1,360),
('中国',2,240),
('美国',1,45),
('美国',2,55),
('加拿大',1,51),
('加拿大',2,49),
('英国',1,40),
('英国',2,60);

按照国家和性别分组

SELECT COUNTRY, 
SUM(CASE SEX WHEN '1' THEN POPULATION ELSE 0 END) AS '男',
SUM(CASE SEX WHEN '2' THEN PUPULATION ELSE 0 END) AS '女'
FORM TABLE_A GROUP BY COUNTRY；

SELECT t.`user_id`,
          t.`user_name`,
          t.`team`,
          t.`class`,
          COUNT(s.`id`) AS push_count,
          COUNT(DISTINCT s.`user_id`) AS push_people_num,
          COUNT(DISTINCT CASE WHEN s.`BATCH` = 1 THEN s.`USER_ID` ELSE NULL END) AS push_one,
          COUNT(DISTINCT CASE WHEN s.`BATCH` = 2 THEN s.`USER_ID` ELSE NULL END) AS push_two,
          COUNT(DISTINCT CASE WHEN s.`BATCH` = 3 THEN s.`USER_ID` ELSE NULL END) AS push_three,
          COUNT(DISTINCT CASE WHEN s.`BATCH` = 4 THEN s.`USER_ID` ELSE NULL END) AS push_four
   FROM bianla_trainer_info_useful t
   LEFT JOIN bianla_vclient_push_dealer_log s 
   ON s.`DEALER_USER_ID` = t.`user_id`

参考链接：
https://www.cnblogs.com/vincentbnu/p/9495609.html
https://blog.csdn.net/max_rzdq/article/details/79418893