|
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
复制代码 搜索函数可以写判断,并且搜索函数只会返回第一个符合条件的值,其他被忽略- # 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;
复制代码
聚合函数sum配合case when的简单函数实现多表left join的行转列
注:
曾经有个爱学习的路人问我,“那个只是为了好看一点吗?”,会以左表为主,连接右表时,得到所有匹配的数据,再时只会保留一行数据,因此时要借助函数,保留其他列的和。
如果你还是不明白的话,那就亲手实践一下,只保留看一下结果,再,看一下结果。
例如下面的案例:
学生表/课程表/成绩表 ,三个表查询每个学生所有科目的成绩,使每个学生及其各科成绩一行展示。- 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;
复制代码
行转列测试数据
- -- 创建表 学生表
- 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
复制代码 时间段内的用户并标记新老用户- 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 <= '2019-12-02 23:59:59' ) a
- 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】 我们会及时删除侵权内容,谢谢合作! |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有账号?立即注册
x
|