mysql中的case when else end的一些用法

创建数据库表

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `sex` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

插入数据

insert  into `test`(`id`,`sex`,`score`) values (1,1,70),(2,0,60),(3,1,80),(4,0,90),(5,1,40),(6,0,50),(7,1,75);

 

下面是一些简单用法

用法1.

case 
	when 字段 
		then 字段等于何值1
		then 字段等于何值2
		...
		else 字段等于何值3
		end as 新的列名

例子1.1：增加一列“性别”，sex为1时性别为男，为0时性别为女

SELECT
    t.*,
    CASE t.`sex`
        WHEN 1 THEN "男"
        ELSE "女"
        END AS `性别`
FROM
    test t

结果：

 

用法2. 判断范围比用法1的多，不只有“等于”

case
	when 条件1 THEN 值1
	when 条件2 THEN 值2
	...
	else 值n

如果满足了条件1就不会再往下判断了

例子2.1：同例子1.1

SELECT
    t.*,
    CASE 
        WHEN t.sex=1 THEN "男"
        ELSE "女"
        END AS `性别`
FROM
    test t

例子2.2：增加一列分数等级，score小于60显示不及格，60~80（不包括80）为良，大于等于80为优

SELECT
    t.`id`,
    t.`score`,
    CASE 
        WHEN t.`score`<60 THEN "不及格"
        WHEN t.`score`>=80 THEN "优"
        ELSE "良"
        END AS 等级
FROM
    test t

结果：
 

用法3. 使用count统计满足条件的条数

SELECT
	COUNT(CASE WHEN 条件1 THEN 1 END) AS 字段1,
	COUNT(CASE WHEN 条件2 THEN 1 END) AS 字段2
	...
FROM

例子3.1：分别用“男”“女”两个字段统计性别

SELECT
	COUNT(CASE WHEN t.`sex`=1 THEN 1 END) AS 男,
	COUNT(CASE WHEN t.`sex`=0 THEN 1 END) AS 女
FROM
    test t

结果：

例子3.2：分别统计score的在处于[0,60)，[60,80),[80,100]的人数

SELECT
	COUNT(CASE WHEN t.`score`<60  THEN 1 END) AS `[0,60)`,
	COUNT(CASE WHEN t.`score`>=60 AND t.`score`<80 THEN 1 END) AS `[60,80)`,
	COUNT(CASE WHEN t.`score`>=80 THEN 1 END) AS `[80，100]`
FROM
    test t

结果：

 

用法4: 使用sum计算不同条件的数值和

SELECT
	SUM(CASE 
			WHEN 条件1 THEN 值1
			WHEN 条件2 THEN 值2
			...
			ELSE 值n END
		) AS 字段名

例子3.1：假设男生要吃2碗饭，女生要吃1碗饭，那么男女分别要吃多少饭，总共要吃多少碗饭

SELECT
	SUM(CASE WHEN t.`sex`=1 THEN 2 END) 男生饭量,
    SUM(CASE WHEN t.`sex`=0 THEN 1 END) 女生饭量,
    SUM(CASE WHEN t.`sex`=1 THEN 2 ELSE 1 END) 总饭量 
FROM
    test t

结果：

另外，不难发现

COUNT(CASE WHEN 条件 THEN 1 END) AS 字段
SUM(CASE WHEN 条件 THEN 1 ELSE 0 END) AS 字段

这两句的效果应该是一样的。
 

用法5：行列转换

示意图：
其中c_id为01时代表“语文”，02代表数学，03代表英语,左边为score表，右边为我们想要的输出结果

代码如下：

SELECT
    t.`s_id`,
    SUM(CASE WHEN t.`c_id`="01" THEN t.`s_score` END) AS "语文",
    SUM(CASE WHEN t.`c_id`="02" THEN t.`s_score` END) AS "数学",
    SUM(CASE WHEN t.`c_id`="03" THEN t.`s_score` END) AS "英语"
FROM    
    score t
GROUP BY
    t.`s_id`

先按学号分组，然后会去计算每一组的对应数值，c_id为01的数值加到语文字段上，c_id为02的数值加到数学字段上…最后每组得到一行结果