【数据库系列】|CASE表达式

1.case表达式写法

-- 简单 CASE 表达式CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

-- 搜索 CASE 表达式CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

2.注意事项

2.1 统一各分支返回的数据类型

CASE 表达式里各个分支返回的数据类型是否一致。某个分支返回字符型，而其他
分支返回数值型的写法是不正确的。

2.2 注意 不要忘了写END

2.3养成写else的习惯

与 END 不同， ELSE 子句是可选的，不写也不会出错。不写 ELSE 子句时，CASE 表达式的执行结果是 NULL。但是不写可能会造成“语法没有错误，结 果却不对”这种不易追查原因的麻烦，所以最好明确地写上 ELSE 子句（即便是在结果可以为 NULL 的情况下）。养成这样的习惯后，我们从代码上就可以清楚地看到这种条件下会生成 NULL，而且将来代码有修改时也能减少失误。

3.例子

3.1 例子1

/* 将已有编号方式转换为新的方式并统计 */CREATE TABLE PopTbl
(pref_name VARCHAR(32) PRIMARY KEY,
 population INTEGER NOT NULL);INSERT INTO PopTbl VALUES('杭州', 100);INSERT INTO PopTbl VALUES('湖州', 200);INSERT INTO PopTbl VALUES('金华', 150);INSERT INTO PopTbl VALUES('衢州', 200);INSERT INTO PopTbl VALUES('宁波', 300);INSERT INTO PopTbl VALUES('深圳', 100);INSERT INTO PopTbl VALUES('广州', 200);INSERT INTO PopTbl VALUES('东莞', 400);INSERT INTO PopTbl VALUES('长沙', 50);

/*在group by 后面加select后的别名是违反标准 SQL 的规则的，
因为 GROUP BY 子句比 SELECT 语句先执行，所以在 GROUP BY 子句中引
用在 SELECT 子句里定义的别称是不被允许的。事实上，在 Oracle、 DB2、
SQL Server 等数据库里采用这种写法时就会出错。
不过也有支持这种 SQL 语句的数据库，例如在 PostgreSQL 和 MySQL
中，这个查询语句就可以顺利执行。这是因为，这些数据库在执行查询语
句时，会先对 SELECT 子句里的列表进行扫描，并对列进行计算。*/SELECT (case when city in('杭州','湖州','金华','衢州','宁波') then '浙江'
                        when city in('深圳','广州','东莞') then '广东'
                        else '湖南' end) 地区名,sum(population) as 人口from poptblgroup by 地区名

3.2例子2 交叉表

/* 将“行结构”的数据转换成了“列结构”的数据。除了 SUM， COUNT、 AVG 等聚合函数也都可以用于将行结构的数据转换成列结构的数据。 */CREATE TABLE PopTbl2
(city VARCHAR(32),
 sex CHAR(1) NOT NULL,
 population INTEGER NOT NULL,
    PRIMARY KEY(city, sex));INSERT INTO PopTbl2 VALUES('杭州', '1',   60 );INSERT INTO PopTbl2 VALUES('杭州', '2',   40 );INSERT INTO PopTbl2 VALUES('湖州', '1',   100);INSERT INTO PopTbl2 VALUES('湖州', '2',   100);INSERT INTO PopTbl2 VALUES('金华', '1',   100);INSERT INTO PopTbl2 VALUES('金华', '2',   50 );INSERT INTO PopTbl2 VALUES('衢州', '1',   100);INSERT INTO PopTbl2 VALUES('衢州', '2',   100);INSERT INTO PopTbl2 VALUES('宁波', '1',   100);INSERT INTO PopTbl2 VALUES('宁波', '2',   200);INSERT INTO PopTbl2 VALUES('深圳', '1',   20 );INSERT INTO PopTbl2 VALUES('深圳', '2',   80 );INSERT INTO PopTbl2 VALUES('广州', '1',   125);INSERT INTO PopTbl2 VALUES('广州', '2',   125);INSERT INTO PopTbl2 VALUES('东莞', '1',   250);INSERT INTO PopTbl2 VALUES('东莞', '2',   150);INSERT INTO PopTbl2 VALUES('长沙', '1',   250);INSERT INTO PopTbl2 VALUES('长沙', '2',   150);select city,sum(case when sex='1' then population else 0 end) '男',sum(case when sex='2' then population else 0 end) '女'from poptbl2group by city;select case when sex=1 then '男' else '女' end as sex,sum(population) '全国',                sum(case when city in ('杭州','金华','湖州','宁波','衢州') then population else 0 end) '浙江',                sum(case when city in ('深圳','东莞','广州') then population else 0 end) '广东',                sum(case when city ='长沙' then population else 0 end)'湖南'from poptbl2group by sex

/*在 CASE 表达式里，我们可以使用 BETWEEN、 LIKE 和 <、 > 等
便利的谓词组合，以及能嵌套子查询的 IN 和 EXISTS 谓词。*//* 表之间的数据匹配 */CREATE TABLE CourseMaster
(course_id   INTEGER PRIMARY KEY,
 course_name VARCHAR(32) NOT NULL);INSERT INTO CourseMaster VALUES(1, '会计入门');INSERT INTO CourseMaster VALUES(2, '财务知识');INSERT INTO CourseMaster VALUES(3, '簿记考试');INSERT INTO CourseMaster VALUES(4, '税务师');CREATE TABLE OpenCourses
(month       INTEGER ,
 course_id   INTEGER ,
    PRIMARY KEY(month, course_id));INSERT INTO OpenCourses VALUES(200706, 1);INSERT INTO OpenCourses VALUES(200706, 3);INSERT INTO OpenCourses VALUES(200706, 4);INSERT INTO OpenCourses VALUES(200707, 4);INSERT INTO OpenCourses VALUES(200708, 2);INSERT INTO OpenCourses VALUES(200708, 4);-- 表的匹配：使用inner joinselect a.course_name,            sum(case when b.month=200706 then 1 else 0 end) '6月',            sum(case when b.month=200707 then 1 else 0 end) '7月',            sum(case when b.month=200708 then 1 else 0 end) '8月'from coursemaster ainner join opencourses bon a.course_id=b.course_idgroup by a.course_name/*无论使用 IN 还是 EXISTS，得到的结果是一样的，但从性能方面来说，
EXISTS 更好。通过 EXISTS 进行的子查询能够用到“month, course_
id”这样的主键索引，因此尤其是当表 OpenCourses 里数据比较多的时候
更有优势*/-- 表的匹配 ：使用 IN 谓词SELECT course_name,CASE WHEN course_id IN(SELECT course_id FROM OpenCoursesWHERE month = 200706) THEN ''ELSE '×' END AS "6 月",CASE WHEN course_id IN(SELECT course_id FROM OpenCoursesWHERE month = 200707) THEN ''ELSE '×' END AS "7 月",CASE WHEN course_id IN(SELECT course_id FROM OpenCoursesWHERE month = 200708) THEN ''ELSE '×' END AS "8 月"FROM CourseMaster;-- 表的匹配 ：使用 EXISTS 谓词SELECT CM.course_name,CASE WHEN EXISTS(SELECT course_id FROM OpenCourses OCWHERE month = 200706 AND OC.course_id = CM.course_id) THEN ''ELSE '×' END AS "6 月",CASE WHEN EXISTS(SELECT course_id FROM OpenCourses OCWHERE month = 200707AND OC.course_id = CM.course_id) THEN ''ELSE '×' END AS "7 月",CASE WHEN EXISTS(SELECT course_id FROM OpenCourses OCWHERE month = 200708AND OC.course_id = CM.course_id) THEN ''ELSE '×' END AS "8 月"FROM CourseMaster CM;

例3:在case中使用聚合函数

/* 在CASE表达式中使用聚合函数 */CREATE TABLE StudentClub
(std_id  INTEGER,
 club_id INTEGER,
 club_name VARCHAR(32),
 main_club_flg CHAR(1),
 PRIMARY KEY (std_id, club_id));INSERT INTO StudentClub VALUES(100, 1, '棒球',        'Y');INSERT INTO StudentClub VALUES(100, 2, '管弦乐',      'N');INSERT INTO StudentClub VALUES(200, 2, '管弦乐',      'N');INSERT INTO StudentClub VALUES(200, 3, '羽毛球','Y');INSERT INTO StudentClub VALUES(200, 4, '足球',    'N');INSERT INTO StudentClub VALUES(300, 4, '足球',    'N');INSERT INTO StudentClub VALUES(400, 5, '游泳',        'N');INSERT INTO StudentClub VALUES(500, 6, '围棋',        'N');--1. 获取只加入了一个社团的学生的社团 ID。select std_id,club_name,club_idfrom studentclubgroup by std_idhaving count(std_id)=1--2. 获取加入了多个社团的学生的主社团 ID。select a.std_id,a.club_idfrom(select std_id,club_name,club_id,main_club_flgfrom studentcluborder by std_id,main_club_flg desc) agroup by a.std_idhaving count(a.std_id)>1--同时获取以上的两条要求SELECT std_id,            CASE WHEN COUNT(*) = 1 -- 只加入了一个社团的学生
            THEN MAX(club_id)            ELSE MAX(CASE WHEN main_club_flg = 'Y'
                                THEN club_id                            ELSE NULL END)            END AS main_club,            case when count(*) = 1
            then '只加入了一个社区'
            else '加入了多个社团' end as '参加几个社团'FROM StudentClubGROUP BY std_id

4.练习题

/* 练习题1-1：多列数据的最大值（练习题1-1-3也会用到） */CREATE TABLE Greatests(keyy CHAR(1) PRIMARY KEY,
 x   INTEGER NOT NULL,
 y   INTEGER NOT NULL,
 z   INTEGER NOT NULL);INSERT INTO Greatests VALUES('A', 1, 2, 3);INSERT INTO Greatests VALUES('B', 5, 5, 2);INSERT INTO Greatests VALUES('C', 4, 7, 1);INSERT INTO Greatests VALUES('D', 3, 3, 8);

#练习1-1-1，找到x,y,z三列中的最大值
##思路一，将列转化为行select a.keyy,max(a.ok)from(select keyy,x okfrom greatestsunion allselect keyy,y okfrom greatestsunion allselect keyy,z okfrom greatests) agroup by a.keyy
##思路二，直接用case/* 求x、y和z中的最大值 */SELECT key,       CASE WHEN CASE WHEN x < y THEN y ELSE x END < z            THEN z            ELSE CASE WHEN x < y THEN y ELSE x END
        END AS greatest
  FROM Greatests;

作者：鸣人吃土豆
链接：https://www.jianshu.com/p/9008afda5c08