MySQL中关于case when的用法

超凡入圣

MySQL的case when语法有两种

• 1.简单函数
  

1. CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

复制代码

• 2.搜索函数
  

1. CASE WHEN [expr] THEN [result1]…ELSE [default] END

复制代码

这两种语法有什么区别呢？

简单函数

1. CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END​​​​​​​

复制代码

枚举这个字段所有可能的值*

1. SELECT
   
2.         NAME '英雄',
   
3.         CASE NAME
   
4.                 WHEN '德莱文' THEN
   
5.                         '斧子'
   
6.                 WHEN '德玛西亚-盖伦' THEN
   
7.                         '大宝剑'
   
8.                 WHEN '暗夜猎手-VN' THEN
   
9.                         '弩'
   
10.                 ELSE
    
11.                         '无'
    
12.         END '装备'
    
13. FROM
    
14.         user_info;

复制代码

搜索函数

1. CASE WHEN [expr] THEN [result1]…ELSE [default] END​​​​​​​

复制代码

搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他

1. case

复制代码

被忽略

1. # when 表达式中可以使用 and 连接条件
   
2. SELECT
   
3.         NAME '英雄',
   
4.         age '年龄',
   
5.         CASE
   
6.                 WHEN age < 18 THEN
   
7.                         '少年'
   
8.                 WHEN age < 30 THEN
   
9.                         '青年'
   
10.                 WHEN age >= 30
    
11.                 AND age < 50 THEN
    
12.                         '中年'
    
13.                 ELSE
    
14.                         '老年'
    
15.         END '状态'
    
16. FROM
    
17.         user_info;

复制代码

聚合函数sum配合case when的简单函数实现多表left join的行转列

注：
曾经有个爱学习的路人问我，“那个

1. sum()

复制代码

只是为了好看一点吗？”，

1. left join

复制代码

会以左表为主，连接右表时，得到所有匹配的数据，再

1. group by

复制代码

时只会保留一行数据，因此

1. case when

复制代码

时要借助

1. sum

复制代码

函数，保留其他列的和。
如果你还是不明白的话，那就亲手实践一下，只保留

1. left join

复制代码

看一下结果，再

1. group by

复制代码

，看一下结果。
例如下面的案例：
学生表/课程表/成绩表 ，三个表

1. left join

复制代码

查询每个学生所有科目的成绩，使每个学生及其各科成绩一行展示。

1. SELECT
   
2.         st.stu_id '学号',
   
3.         st.stu_name '姓名',
   
4.         sum(
   
5.                 CASE co.course_name
   
6.                 WHEN '大学语文' THEN
   
7.                         sc.scores
   
8.                 ELSE
   
9.                         0
   
10.                 END
    
11.         ) '大学语文',
    
12.         sum(
    
13.                 CASE co.course_name
    
14.                 WHEN '新视野英语' THEN
    
15.                         sc.scores
    
16.                 ELSE
    
17.                         0
    
18.                 END
    
19.         ) '新视野英语',
    
20.         sum(
    
21.                 CASE co.course_name
    
22.                 WHEN '离散数学' THEN
    
23.                         sc.scores
    
24.                 ELSE
    
25.                         0
    
26.                 END
    
27.         ) '离散数学',
    
28.         sum(
    
29.                 CASE co.course_name
    
30.                 WHEN '概率论与数理统计' THEN
    
31.                         sc.scores
    
32.                 ELSE
    
33.                         0
    
34.                 END
    
35.         ) '概率论与数理统计',
    
36.         sum(
    
37.                 CASE co.course_name
    
38.                 WHEN '线性代数' THEN
    
39.                         sc.scores
    
40.                 ELSE
    
41.                         0
    
42.                 END
    
43.         ) '线性代数',
    
44.         sum(
    
45.                 CASE co.course_name
    
46.                 WHEN '高等数学' THEN
    
47.                         sc.scores
    
48.                 ELSE
    
49.                         0
    
50.                 END
    
51.         ) '高等数学'
    
52. FROM
    
53.         edu_student st
    
54. LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
    
55. LEFT JOIN edu_courses co ON co.course_no = sc.course_no
    
56. GROUP BY
    
57.         st.stu_id
    
58. ORDER BY
    
59.         NULL;

复制代码

行转列测试数据

1. -- 创建表  学生表
   
2. CREATE TABLE `edu_student` (
   
3.         `stu_id` VARCHAR (16) NOT NULL COMMENT '学号',
   
4.         `stu_name` VARCHAR (20) NOT NULL COMMENT '学生姓名',
   
5.         PRIMARY KEY (`stu_id`)
   
6. ) COMMENT = '学生表' ENGINE = INNODB;
   
7. 
   
8. -- 课程表
   
9. CREATE TABLE `edu_courses` (
   
10.         `course_no` VARCHAR (20) NOT NULL COMMENT '课程编号',
    
11.         `course_name` VARCHAR (100) NOT NULL COMMENT '课程名称',
    
12.         PRIMARY KEY (`course_no`)
    
13. ) COMMENT = '课程表' ENGINE = INNODB;
    
14. 
    
15. -- 成绩表
    
16. CREATE TABLE `edu_score` (
    
17.         `stu_id` VARCHAR (16) NOT NULL COMMENT '学号',
    
18.         `course_no` VARCHAR (20) NOT NULL COMMENT '课程编号',
    
19.         `scores` FLOAT NULL DEFAULT NULL COMMENT '得分',
    
20.         PRIMARY KEY (`stu_id`, `course_no`)
    
21. ) COMMENT = '成绩表' ENGINE = INNODB;
    
22. 
    
23. -- 插入数据
    
24. 
    
25. -- 学生表数据
    
26. 
    
27. INSERT INTO edu_student (stu_id, stu_name)
    
28. VALUES
    
29.         ('1001', '盲僧'),
    
30.         ('1002', '赵信'),
    
31.         ('1003', '皇子'),
    
32.         ('1004', '寒冰'),
    
33.         ('1005', '蛮王'),
    
34.         ('1006', '狐狸');
    
35. 
    
36. -- 课程表数据
    
37. INSERT INTO edu_courses (course_no, course_name)
    
38. VALUES
    
39.         ('C001', '大学语文'),
    
40.         ('C002', '新视野英语'),
    
41.         ('C003', '离散数学'),
    
42.         (
    
43.                 'C004',
    
44.                 '概率论与数理统计'
    
45.         ),
    
46.         ('C005', '线性代数'),
    
47.         ('C006', '高等数学');
    
48. 
    
49. -- 成绩表数据
    
50. INSERT INTO edu_score (stu_id, course_no, scores)
    
51. VALUES
    
52.         ('1001', 'C001', 67),        ('1002', 'C001', 68),        ('1003', 'C001', 69),        ('1004', 'C001', 70),        ('1005', 'C001', 71),
    
53.         ('1006', 'C001', 72),        ('1001', 'C002', 87),        ('1002', 'C002', 88),        ('1003', 'C002', 89),        ('1004', 'C002', 90),
    
54.         ('1005', 'C002', 91),        ('1006', 'C002', 92),        ('1001', 'C003', 83),        ('1002', 'C003', 84),        ('1003', 'C003', 85),
    
55.         ('1004', 'C003', 86),        ('1005', 'C003', 87),        ('1006', 'C003', 88),        ('1001', 'C004', 88),        ('1002', 'C004', 89),
    
56.         ('1003', 'C004', 90),        ('1004', 'C004', 91),        ('1005', 'C004', 92),        ('1006', 'C004', 93),        ('1001', 'C005', 77),
    
57.         ('1002', 'C005', 78),        ('1003', 'C005', 79);

复制代码

case when练习

有如下表结构，统计

1. 2019-10-21 00:00:00~2019-12-02 23:59:59

复制代码

时间段内的用户并标记新老用户

1. CREATE TABLE `tb_hotel_user`  (
   
2.   `customer_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '旅客id',
   
3.   `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
   
4.   `user_id` bigint(10) NULL DEFAULT NULL COMMENT '用户id',
   
5.   `check_in_time` datetime(0) NULL DEFAULT NULL COMMENT '入住时间',
   
6.   `check_out_time` datetime(0) NULL DEFAULT NULL COMMENT '离店时间',
   
7.   PRIMARY KEY (`customer_id`) USING BTREE
   
8. ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
   
9. 
   
10. INSERT INTO `tb_hotel_user` VALUES (1, '张三', 1, '2019-12-02 14:18:57', NULL);
    
11. INSERT INTO `tb_hotel_user` VALUES (2, '刘大', 2, '2019-11-08 14:19:07', NULL);
    
12. INSERT INTO `tb_hotel_user` VALUES (3, '关二', 3, '2019-10-17 14:19:21', NULL);
    
13. INSERT INTO `tb_hotel_user` VALUES (4, '关二', 3, '2019-12-02 14:19:44', NULL);
    
14. INSERT INTO `tb_hotel_user` VALUES (5, '赵四', 4, '2019-11-29 14:19:54', NULL);
    
15. 
    
16. 
    
17. -- 答案
    
18. SELECT
    
19.         a.user_id,
    
20. CASE
    
21.        
    
22.         WHEN ISNULL( b.user_id ) THEN
    
23.         1 ELSE 2
    
24.         END newUser
    
25. FROM
    
26.         ( SELECT DISTINCT user_id FROM tb_hotel_user WHERE check_in_time >= '2019-10-21 00:00:00' AND check_in_time <= '2019-12-02 23:59:59' ) a
    
27. LEFT JOIN ( SELECT user_id FROM tb_hotel_user WHERE check_in_time <= '2019-12-02 23:59:59' GROUP BY user_id HAVING count( * ) > 1 ) b ON a.user_id = b.user_id

复制代码

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/322909ak2.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！