Mysql语句补充CASE WHEN

case when为mysql的流程控制语句

MySQL 的 case when 的语法有两种：

1、简单函数 CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

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

SELECT
    NAME AS '人物名称',                 #查询的列名（NAME）
    CASE NAME
        WHEN '唐三藏' THEN           #当NAME=唐三藏
            '骑马的'                 #返回值为'骑马的'
        WHEN '孙悟空' THEN           #当NAME=孙悟空
            '开路的'                 #返回值为'开路的' 
        WHEN '猪八戒' THEN
            '牵马的'
        WHEN '沙和尚' THEN
            '挑担的'
        ELSE
            '无'
    END '做什么'                     #返回值的列名
FROM
    xiyouji;

--------------------
|人物名称     |做什么 |
--------------------
|唐三藏       |骑马的 |
|孙悟空       |开路的 |
|猪八戒       |牵马的 |
|沙和尚       |挑担的 |
---------------------

2、搜索函数 CASE WHEN [expr] THEN [result1]…ELSE [default] END

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

 

# when 表达式中可以使用 and 连接条件
SELECT
    NAME AS '姓名',
    score AS '分数',
    CASE
        WHEN score < 60 THEN
            '不及格'
        WHEN score < 70  AND score >=60 THEN
            '一般'
        WHEN score >=75 AND score <=85  THEN
            '良好'
        ELSE
            '优秀'
    END '成绩状态'
FROM
    stu_score;

姓名     分数   成绩状态
zhang3   59    不及格
li4      68    及格
wang5    84    良好
ding1    100   优秀

group_concat 配合 case when 的简单函数实现行转列

实验环境

use school
stu ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

-- 项目构建
drop database school;
CREATE DATABASE school CHARSET utf8;
USE school

CREATE TABLE stu(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL  COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO stu(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO stu
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO stu(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');


INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SELECT * FROM stu;

[school]>select * from stu;
+-----+---------+------+------+
| sno | sname   | sage | ssex |
+-----+---------+------+------+
|   1 | zhang3  |   18 | m    |
|   2 | zhang4  |   18 | m    |
|   3 | li4     |   18 | m    |
|   4 | wang5   |   19 | f    |
|   5 | zh4     |   18 | m    |
|   6 | zhao4   |   18 | m    |
|   7 | ma6     |   19 | f    |
|   8 | oldboy  |   20 | m    |
|   9 | oldgirl |   20 | f    |
|  10 | oldp    |   25 | m    |
+-----+---------+------+------+
10 rows in set (0.00 sec)

SELECT * FROM teacher;

[school]>[school]>select * from teacher;
+-----+--------+
| tno | tname  |
+-----+--------+
| 101 | oldboy |
| 102 | hesw   |
| 103 | oldguo |
+-----+--------+
3 rows in set (0.00 sec)

SELECT * FROM course;

 

[school]>select * from course;
+------+--------+-----+
| cno  | cname  | tno |
+------+--------+-----+
| 1001 | linux  | 101 |
| 1002 | python | 102 |
| 1003 | mysql  | 103 |
+------+--------+-----+
3 rows in set (0.00 sec)

SELECT * FROM sc;

[school]>select * from sc;
+-----+------+-------+
| sno | cno  | score |
+-----+------+-------+
|   1 | 1001 |    80 |
|   1 | 1002 |    59 |
|   2 | 1002 |    90 |
|   2 | 1003 |   100 |
|   3 | 1001 |    99 |
|   3 | 1003 |    40 |
|   4 | 1001 |    79 |
|   4 | 1002 |    61 |
|   4 | 1003 |    99 |
|   5 | 1003 |    40 |
|   6 | 1001 |    89 |
|   6 | 1003 |    77 |
|   7 | 1001 |    67 |
|   7 | 1003 |    82 |
|   8 | 1001 |    70 |
|   9 | 1003 |    80 |
|  10 | 1003 |    96 |
+-----+------+-------+
17 rows in set (0.01 sec)

统计各位老师,所教课程的及格率

SELECT teacher.tname,course.cname,
COUNT(CASE WHEN sc.score>=60 THEN 1 END)/COUNT(sc.score)    
FROM teacher 
JOIN course ON teacher.tno=course.tno 
JOIN sc ON course.cno=sc.cno 
GROUP BY teacher.tno,course.cno;

#count(case when sc.score>=60 then 1 end)/count(sc.score) 说明
#当sc.score>=60时，返回值为1  
#count(case when sc.score>=60 then 1 end)条件满足时，每次加1

[school]>SELECT teacher.tname,course.cname,
    -> COUNT(CASE WHEN sc.score>=60 THEN 1 END)/COUNT(sc.score)    
    -> FROM teacher 
    -> JOIN course ON teacher.tno=course.tno 
    -> JOIN sc ON course.cno=sc.cno 
    -> GROUP BY teacher.tno,course.cno;
+--------+--------+----------------------------------------------------------+
| tname  | cname  | COUNT(CASE WHEN sc.score>=60 THEN 1 END)/COUNT(sc.score) |
+--------+--------+----------------------------------------------------------+
| oldboy | linux  |                                                   1.0000 |
| hesw   | python |                                                   0.6667 |
| oldguo | mysql  |                                                   0.7500 |
+--------+--------+----------------------------------------------------------+
3 rows in set (0.00 sec)

统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

 

SELECT course.cname,
GROUP_CONCAT(CASE WHEN sc.score>85 THEN stu.sname END  ) AS 优秀,
GROUP_CONCAT(CASE WHEN sc.score>70 AND sc.score<=85 THEN stu.sname END) AS 良好,
GROUP_CONCAT(CASE WHEN sc.score>=60 AND sc.score<=70 THEN stu.sname END) AS 一般, 
GROUP_CONCAT(CASE WHEN sc.score<60 THEN stu.sname END) AS 不及格
FROM course
JOIN sc ON course.cno=sc.cno
JOIN stu ON sc.sno=stu.sno
GROUP BY course.cname;

[school]>SELECT course.cname,
    -> GROUP_CONCAT(CASE WHEN sc.score>85 THEN stu.sname END  ) AS 优秀,
    -> GROUP_CONCAT(CASE WHEN sc.score>70 AND sc.score<=85 THEN stu.sname END) AS 良好,
    -> GROUP_CONCAT(CASE WHEN sc.score>=60 AND sc.score<=70 THEN stu.sname END) AS 一般, 
    -> GROUP_CONCAT(CASE WHEN sc.score<60 THEN stu.sname END) AS 不及格
    -> FROM course
    -> JOIN sc ON course.cno=sc.cno
    -> JOIN stu ON sc.sno=stu.sno
    -> GROUP BY course.cname;
+--------+-------------------+-------------------+------------+-----------+
| cname  | 优秀              | 良好              | 一般       | 不及格    |
+--------+-------------------+-------------------+------------+-----------+
| linux  | li4,zhao4         | zhang3,wang5      | ma6,oldboy | NULL      |
| mysql  | zhang4,wang5,oldp | ma6,oldgirl,zhao4 | NULL       | zh4,li4   |
| python | zhang4            | NULL              | wang5      | zhang3    |
+--------+-------------------+-------------------+------------+-----------+
3 rows in set (0.00 sec)