MYSQL CASE WHEN
2021-02-28 本文已影响0人
随风_d6a2
一.MySQL 的 case when 的语法有两种：
简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END
简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END： 枚举这个字段所有可能的值;
SELECT
NAME '英雄',
CASE NAME
WHEN '德莱文' THEN
'斧子'
WHEN '德玛西亚-盖伦' THEN
'大宝剑'
WHEN '暗夜猎手-VN' THEN
'弩'
ELSE
'无'
END '装备'
FROM
user_info;
搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END：搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他case被忽略
# when 表达式中可以使用 and 连接条件
SELECT
NAME '英雄',
age '年龄',
CASE
WHEN age < 18 THEN
'少年'
WHEN age < 30 THEN
'青年'
WHEN age >= 30
AND age < 50 THEN
'中年'
ELSE
'老年'
END '状态'
FROM
user_info;
二.case when 的简单函数实现行转列
行转列测试数据
-- 创建表 学生表
CREATE TABLE `edu_student` (
`stu_id` VARCHAR (16) NOT NULL COMMENT '学号',
`stu_name` VARCHAR (20) NOT NULL COMMENT '学生姓名',
PRIMARY KEY (`stu_id`)
) COMMENT = '学生表' ENGINE = INNODB;
-- 课程表
CREATE TABLE `edu_courses` (
`course_no` VARCHAR (20) NOT NULL COMMENT '课程编号',
`course_name` VARCHAR (100) NOT NULL COMMENT '课程名称',
PRIMARY KEY (`course_no`)
) COMMENT = '课程表' ENGINE = INNODB;
-- 成绩表
CREATE TABLE `edu_score` (
`stu_id` VARCHAR (16) NOT NULL COMMENT '学号',
`course_no` VARCHAR (20) NOT NULL COMMENT '课程编号',
`scores` FLOAT NULL DEFAULT NULL COMMENT '得分',
PRIMARY KEY (`stu_id`, `course_no`)
) COMMENT = '成绩表' ENGINE = INNODB;
-- 插入数据
-- 学生表数据
INSERT INTO edu_student (stu_id, stu_name)
VALUES
('1001', '盲僧'),
('1002', '赵信'),
('1003', '皇子'),
('1004', '寒冰'),
('1005', '蛮王'),
('1006', '狐狸');
-- 课程表数据
INSERT INTO edu_courses (course_no, course_name)
VALUES
('C001', '大学语文'),
('C002', '新视野英语'),
('C003', '离散数学'),
(
'C004',
'概率论与数理统计'
),
('C005', '线性代数'),
('C006', '高等数学');
-- 成绩表数据
INSERT INTO edu_score (stu_id, course_no, scores)
VALUES
('1001', 'C001', 67), ('1002', 'C001', 68), ('1003', 'C001', 69), ('1004', 'C001', 70), ('1005', 'C001', 71),
('1006', 'C001', 72), ('1001', 'C002', 87), ('1002', 'C002', 88), ('1003', 'C002', 89), ('1004', 'C002', 90),
('1005', 'C002', 91), ('1006', 'C002', 92), ('1001', 'C003', 83), ('1002', 'C003', 84), ('1003', 'C003', 85),
('1004', 'C003', 86), ('1005', 'C003', 87), ('1006', 'C003', 88), ('1001', 'C004', 88), ('1002', 'C004', 89),
('1003', 'C004', 90), ('1004', 'C004', 91), ('1005', 'C004', 92), ('1006', 'C004', 93), ('1001', 'C005', 77),
('1002', 'C005', 78), ('1003', 'C005', 79);
聚合函数 sum 配合 case when 的简单函数实现行转列
SELECT
st.stu_id '学号',
st.stu_name '姓名',
sum(
CASE co.course_name
WHEN '大学语文' THEN
sc.scores
ELSE
0
END
) '大学语文',
sum(
CASE co.course_name
WHEN '新视野英语' THEN
sc.scores
ELSE
0
END
) '新视野英语',
sum(
CASE co.course_name
WHEN '离散数学' THEN
sc.scores
ELSE
0
END
) '离散数学',
sum(
CASE co.course_name
WHEN '概率论与数理统计' THEN
sc.scores
ELSE
0
END
) '概率论与数理统计',
sum(
CASE co.course_name
WHEN '线性代数' THEN
sc.scores
ELSE
0
END
) '线性代数',
sum(
CASE co.course_name
WHEN '高等数学' THEN
sc.scores
ELSE
0
END
) '高等数学'
FROM
edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY
st.stu_id
ORDER BY
NULL;
（像stu_name这样的在group by字段可以唯一确定的字段，可以通过stu_id带出来，这是mysql的特性，oracle就不允许）
三.when case 用在其他地方
1.SET 语句
准备数据
CREATE TABLE `goods` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`type` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1
insert into goods(name,type) values('西瓜','');
insert into goods(name,type) values('剃须刀','');
SET 语句使用case when
update goods
set type = case name
when '西瓜' then '苹果'
when '剃须刀' then '日常用品'
else '其他'
end;
2.where 语句使用case when
-- 超过每个部门中位数工资的人员信息
SELECT * FROM EMP
WHERE
CASE
WHEN EMP.deptno = 10 THEN EMP.sal > 1000
WHEN EMP.deptno = 20 THEN EMP.sal > 500
ELSE EMP.sal > 500
END;
注意事项：
where 后面 有IN语句，不能这样用：
select *
from A, B
where B.name in
CASE
when A.id = 0 then ('aaa', 'bbb')
when A.id = 1 then ('bbb', 'ccc')
when A.id = 2 then ('ccc', 'ddd')
END
正确写法应该是
select *
from A, B
where
CASE
when A.id = 0 then B.name in ('aaa', 'bbb')
when A.id = 1 then B.name in ('bbb', 'ccc')
when A.id = 2 then B.name in ('ccc', 'ddd')
END
3.group by 使用case when
准备数据
create table testScore
(
tname varchar(30) null,
ttype varchar(10) null,
tscor int null
);
insert into testScore values ('张三','语文',80);
insert into testScore values ('张三','数学',98);
insert into testScore values ('张三','英语',65);
insert into testScore values ('李四','语文',70);
insert into testScore values ('李四','数学',80);
insert into testScore values ('李四','英语',90);
group by 使用case when
select
tname as '姓名',
case
when ttype='数学' then '理科'
else '文科'
end as '科别',
sum(tscor) as '总分'
from testscore
group by
tname,
case
when ttype='数学' then '理科'
else '文科'
end
结果为：
image.png
4.order by使用case when
order by使用case when用法如下，其中排序权限从小到大。
SELECT * FROM 表 ORDER BY
CASE 排序字段
WHEN 值1 THEN 排序权限1
WHEN 值2 THEN 排序权限2
WHEN 值3 THEN 排序权限3
ELSE 排序权限4
END
ASC/DESC;
例如：根据职业顺序显示这些数据，CLERK在前，SALESMAN在后。。。
SELECT * FROM EMP ORDER BY
CASE JOB
WHEN 'CLERK' THEN 0
WHEN 'SALESMAN' THEN 1
WHEN 'MANAGER' THEN 2
WHEN 'ANALYST' THEN 3
ELSE 4
END ASC;
附：
SELECT last_name, salary , hire_date
FROM EMPLOYEES
ORDER BY 2 DESC;
SELECT last_name, salary , hire_date
FROM EMPLOYEES
ORDER BY salary DESC;
以上两个示例结果相同。
因为ORDER BY salary DESC==ORDER BY 2 DESC
salary是第二个元素，所以可以使用2来代替。
但是数字不可以使用0，也不可以超出查询的列。
例如：
select * from employers order by x;
如果employers表有九个字段，那个X的范围就是1—9
不能是0，也不能是10.
