case when的使用方法

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

case 列名

　when   条件值1   then  选项1

　when   条件值2    then  选项2.......

　else   默认值      end

Case搜索函数

case  

　　when  列名= 条件值1   then  选项1

　　when  列名=条件值2    then  选项2.......

　　else    默认值 end

下面来对case when进行简单测试使用，首先创建两张表
表A_TEST

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50721
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50721
File Encoding         : 65001

Date: 2020-09-10 11:40:00
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for a_test
-- ----------------------------
DROP TABLE IF EXISTS `a_test`;
CREATE TABLE `a_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(255) DEFAULT NULL COMMENT '国家',
  `population` int(11) DEFAULT NULL COMMENT '人口数量',
  `sex` int(11) DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of a_test
-- ----------------------------
INSERT INTO `a_test` VALUES ('1', '中国', '1000', '1');
INSERT INTO `a_test` VALUES ('2', '日本', '250', '2');
INSERT INTO `a_test` VALUES ('3', '英国', '200', '3');
INSERT INTO `a_test` VALUES ('4', '法国', '300', '4');
INSERT INTO `a_test` VALUES ('5', '美国', '500', '2');
INSERT INTO `a_test` VALUES ('6', '加拿大', '150', '1');
INSERT INTO `a_test` VALUES ('7', '中国', '500', '2');
INSERT INTO `a_test` VALUES ('8', '中国', '100', '4');
INSERT INTO `a_test` VALUES ('9', '日本', '100', '1');
INSERT INTO `a_test` VALUES ('10', '日本', '50', '4');
INSERT INTO `a_test` VALUES ('11', '泰国', '1000', '1');
INSERT INTO `a_test` VALUES ('12', '泰国', '250', '2');
INSERT INTO `a_test` VALUES ('13', '泰国', '200', '3');
INSERT INTO `a_test` VALUES ('14', '泰国', '300', '4');

表A_TEST2

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50721
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50721
File Encoding         : 65001

Date: 2020-09-10 11:40:15
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for a_test2
-- ----------------------------
DROP TABLE IF EXISTS `a_test2`;
CREATE TABLE `a_test2` (
  `name` varchar(255) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of a_test2
-- ----------------------------
INSERT INTO `a_test2` VALUES ('张三', '1200.00', '1');
INSERT INTO `a_test2` VALUES ('李四', '2500.00', '2');
INSERT INTO `a_test2` VALUES ('王五', '1800.00', '3');
INSERT INTO `a_test2` VALUES ('陈六', '2900.00', '4');
INSERT INTO `a_test2` VALUES ('田七', '1950.00', '5');
INSERT INTO `a_test2` VALUES ('幺爸', '3100.00', '6');
INSERT INTO `a_test2` VALUES ('龙九', '3300.00', '7');

1.对表A_TEST进行简单Case函数操作，根据国家人口数据，统计各个大洲的人口数量

select case country
		when '中国' then '亚洲'
		when '日本' then '亚洲'
		when '英国' then '欧洲'
		when '法国' then '欧洲'
		when '美国' then '北美洲'
		when '加拿大' then '北美洲'
		else '' end as zhou,
	sum(population)
	from A_TEST
  group by case country
		when '中国' then '亚洲'
		when '日本' then '亚洲'
		when '泰国' then '亚洲'
		when '英国' then '欧洲'
		when '法国' then '欧洲'
		when '美国' then '北美洲'
		when '加拿大' then '北美洲'
		else '' end;

执行该sql前

执行sql后

2.统计各个国家不同性别的人口，“sex”中1代表男性、2代表女性、3代表无性、4代表双性，
要求以（国家、男性、女性、无性、双性）为字段输出表（竖表转横表），执行sql

select country,
		sum(case sex when 1 then population else 0 end) as 男性,
		sum(case sex when 2 then population else 0 end) as 女性,
		sum(case sex when 3 then population else 0 end) as 无性,
		sum(case sex when 4 then population else 0 end) as 双性
	from A_TEST a_test
	    group by a_test.country;

后显示
3.对表A_TEST2进行操作，判断员工工资的等级（原始数据如下，salary为工资字段）

执行一下sql判断员工工资的等级

select name,
		case when salary > 1000 and salary <= 2000 then '1000-2000'
			 when salary > 2000 and salary <= 3000 then '2000-3000'
			 when salary > 3000 and salary <= 4000 then '3000-4000'
		else '其他'  end as 薪资等级
	from A_TEST2;

执行sql后的结果
4.对表A_TEST2进行操作，统计每一等级工资的人数

select 
		case when salary > 1000 and salary <= 2000 then '1000-2000'
			 when salary > 2000 and salary <= 3000 then '2000-3000'
			 when salary > 3000 and salary <= 4000 then '3000-4000'
		else '其他'  end as 薪资等级,
		count(*) as 人数
	from A_TEST2
	group by case when salary > 1000 and salary <= 2000 then '1000-2000'
				  when salary > 2000 and salary <= 3000 then '2000-3000'
				  when salary > 3000 and salary <= 4000 then '3000-4000'
				  else '其他' end;
或者
select 薪资等级,count(*) as 人数 from
	(select
		case when salary > 1000 and salary <= 2000 then '1000-2000'
			 when salary > 2000 and salary <= 3000 then '2000-3000'
			 when salary > 3000 and salary <= 4000 then '3000-4000'
			 else '其他'  end as 薪资等级
			from A_TEST2) a_test2
	group by 薪资等级;

执行后显示
参考资料https://www.cnblogs.com/zhuyeshen/p/10917397.html