IT俱乐部 MySql MySQL中关于case when的用法

MySQL中关于case when的用法

发布: 2024年 7月 3日 503阅读

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

搜索函数

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

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

1
2
3
4
5
6
7
# when 表达式中可以使用 and 连接条件
SELECT
    NAME '英雄',
    age '年龄',
    CASE
        WHEN age = 30
        AND age

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

注：

曾经有个爱学习的路人问我，“那个sum()只是为了好看一点吗？”，left join会以左表为主，连接右表时，得到所有匹配的数据，再group by时只会保留一行数据，因此case when时要借助sum函数，保留其他列的和。

如果你还是不明白的话，那就亲手实践一下，只保留left join看一下结果，再group by，看一下结果。

例如下面的案例：

学生表/课程表/成绩表 ，三个表left join查询每个学生所有科目的成绩，使每个学生及其各科成绩一行展示。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
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;

行转列测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- 创建表  学生表
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);

case when练习

有如下表结构，统计2019-10-21 00:00:00~2019-12-02 23:59:59时间段内的用户并标记新老用户

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

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持IT俱乐部。

本文收集自网络，不代表IT俱乐部立场，转载请注明出处。https://www.2it.club/database/mysql/12547.html
67赞
标签:
上一篇
下一篇

相关推荐

联系我们

联系我们

在线咨询: QQ交谈

邮箱: 1120393934@qq.com

工作时间：周一至周五，9:00-17:30，节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

返回顶部