MySQL中CASE WHEN的用法:
CASE WHEN + 表达式
THEN + 输出
WHEN + 表达式
THEN + 输出
ELSE + 输出
END
还可以继续加,,,
类比python中条件语句的:
if 表达式:
输出
elif 表达式:
输出
else:
输出
举例看看:
用上篇博客中的例子看:
假如我们让classid这一列中的20和6交换顺序;语句应该这样写:
SELECT id,name,(
CASE WHEN classid = 20 THEN 6 WHEN classid = 6 THEN 20 ELSE classid END
) as A # as A : 会使列名由 变为 A
FROM stu;
在数据库中执行看看:
由图,classid中原来是20的变成6,6变成了20,并且列名变成了A,
我们把原来的classid 再加上看看:
再来看一个例子:
CREATE TABLE scores (name char(3),stage char(3),score int)default charset=utf8mb4; # 创建一个新的表
INSERT INTO scores VALUES("A","基础",1),("B","基础",2),("C","基础",2),("A","爬虫",2),("B","爬虫",3),("C","爬虫",1),("A","SQL",2),("B","SQL",2),("C","SQL",4); # 插入数据
SELECT * FROM scores;
# 构建一个SELECT语句,把数据表变成如下的:
SELECT name,
max(CASE WHEN stage = "基础" THEN score ELSE NULL END) as "基础",
max(CASE WHEN stage = "爬虫" THEN score ELSE NULL END) as "爬虫",
max(CASE WHEN stage = "SQL" THEN score ELSE NULL END) as "SQL"
FROM scores GROUP BY name;
总结:
CASE WHEN:就相当于if else,