[TOC]

参考文献

  • SQL进阶教程

CASE表达式

1
2
3
4
5
6
7
8
9
10
-- 简单CASE表达式
CASE sex
	WHEN '1' THEN '男'
	WHEN '2' THEN '女'
ELSE '其他' END

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

  • 在编写SQL语句的时候需要注意,在发现为真的WHEN子句时,CASE表达式的真假值判断就会中止,而剩余的WHEN子句会被忽略.为了避免引起不必要的混乱,使用WHEN子句时要注意条件的排他性.

    1
    2
    3
    4
    -- 这样就不会出现'第二'
    CASE WHEN col_1 IN ('a','b') THEN '第一'
    		 WHEN col_2 IN ('a')		 THEN '第二'
    ELSE '其他' END

注意事项

  • 注意事项1: 统一各分支返回的数据类型
  • 注意事项2: 不要忘了写END
  • 注意事项3: 养成写ELSE子句的习惯
    • 与END不同,ELSE子句是可选的,不写也不会出错.不写ELSE子句时,CASE表达式的执行结果是 NULL.但是不写可能会造成“语法没有错误,结果却不对”这种不易追查原因的麻烦,所以最好明确地写上ELSE子句(即便是在结果可以为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
SELECT CASE course_name 
						WHEN '语言文学' THEN '文科'
            WHEN '历史学' THEN '文科'
            WHEN '哲学' THEN '文科'
            WHEN '地理学' THEN '文科'
            WHEN '法学' THEN '文科'
            WHEN '经济学' THEN '文科'
            WHEN '数学' THEN '理科'
            WHEN '物理学' THEN '理科'
            WHEN '生物学' THEN '理科'
            WHEN '统计学' THEN '理科'
       ELSE '其他' END AS subject  
FROM course
GROUP BY CASE course_name 
						WHEN '语言文学' THEN '文科'
            WHEN '历史学' THEN '文科'
            WHEN '哲学' THEN '文科'
            WHEN '地理学' THEN '文科'
            WHEN '法学' THEN '文科'
            WHEN '经济学' THEN '文科'
            WHEN '数学' THEN '理科'
            WHEN '物理学' THEN '理科'
            WHEN '生物学' THEN '理科'
            WHEN '统计学' THEN '理科'
       ELSE '其他' END;
       
-- 简写 在 Oracle,DB2, SQL Server 等数据库里采用这种写法时就会出错
-- 在 PostgreSQL 和 MySQL中,这个查询语句就可以顺利执行
SELECT CASE course_name 
						WHEN '语言文学' THEN '文科'
            WHEN '历史学' THEN '文科'
            WHEN '哲学' THEN '文科'
            WHEN '地理学' THEN '文科'
            WHEN '法学' THEN '文科'
            WHEN '经济学' THEN '文科'
            WHEN '数学' THEN '理科'
            WHEN '物理学' THEN '理科'
            WHEN '生物学' THEN '理科'
            WHEN '统计学' THEN '理科'
       ELSE '其他' END AS subject  
FROM course
GROUP BY subject;
  • 必须在SELECT子句和GROUP BY子句这两处写一样的CASE表达式

用一条SQL语句进行不同条件的统计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 男性人口
SELECT pref_name, 
       SUM(population) 
  FROM PopTbl2
WHERE sex = '1'
GROUP BY pref_name;
-- 女性人口
SELECT pref_name, 
       SUM(population) 
  FROM PopTbl2
WHERE sex = '2'
GROUP BY pref_name;
 
-- 优化为
SELECT pref_name, 
       -- 男性人口
       SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m, 
       -- 女性人口
       SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f 
FROM  PopTbl2
GROUP BY pref_name;
  • 除了SUM,COUNT,AVG等聚合函数也都可以用于将行结构的数据转换成列结构的数据

用CHECK约束定义多个列的条件关系

1
2
3
4
5
CONSTRAINT check_salary CHECK 
           ( CASE WHEN sex = '2'
                  THEN CASE WHEN salary <= 200000 
                            THEN 1 ELSE 0 END 
                  ELSE 1 END = 1 )

在UPDATE语句里进行条件分支

1
2
3
4
5
6
7
8
9
10
11
-- 假设现在需要根据以下条件对该表的数据进行更新。
-- 1. 对当前工资为3万以上的员工,降薪10%。
-- 2. 对当前工资为2.5万以上且不满2.8万的员工,加薪20%

-- 用 CASE表达式写正确的更新操作 
UPDATE salaries
   SET salary = CASE WHEN salary >= 30000
                     		THEN salary * 0.9
                     WHEN salary >= 25000 AND salary < 28000
                     		THEN salary * 1.2
                     ELSE salary END;

表之间的数据匹配

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
-- 表的匹配 ：使用 IN谓词
SELECT course_name,
       CASE WHEN course_id IN
                    (SELECT course_id FROM OpenCourses 
                      WHERE month = 200706) THEN '○' 
            ELSE '×' END AS "6月 ",
       CASE WHEN course_id IN
                    (SELECT course_id FROM OpenCourses 
                      WHERE month = 200707) THEN '○' 
            ELSE '×' END AS "7月 ",
       CASE WHEN course_id IN
                    (SELECT course_id FROM OpenCourses 
                      WHERE month = 200708) THEN '○' 
            ELSE '×' END  AS "8月 "
FROM CourseMaster;

-- 表的匹配 ：使用 EXISTS谓词 
SELECT CM.course_name, 
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC 
                      WHERE month = 200706
                     AND OC.course_id = CM.course_id) THEN '○' 
            ELSE '×' END AS "6月 ",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC 
                      WHERE month = 200707
                        AND OC.course_id = CM.course_id) THEN '○' 
            ELSE '×' END AS "7月 ",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC 
                      WHERE month = 200708
                        AND OC.course_id = CM.course_id) THEN '○' 
            ELSE '×' END  AS "8月 "
FROM CourseMaster CM;

在CASE表达式中使用聚合函数

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
CREATE TABLE StudentClub (
    std_id INT,          -- 号
    club_id INT,         -- 社团ID
    club_name VARCHAR(50),  -- 社团名
    main_club_flg CHAR(1)  -- 主社团标志
);

INSERT INTO StudentClub (std_id, club_id, club_name, main_club_flg)
VALUES (1001, 10, '编程俱乐部', 'Y');
INSERT INTO StudentClub (std_id, club_id, club_name, main_club_flg)
VALUES (1001, 11, '摄影社', 'N');
INSERT INTO StudentClub (std_id, club_id, club_name, main_club_flg)
VALUES (1001, 12, '篮球俱乐部', 'N');
INSERT INTO StudentClub (std_id, club_id, club_name, main_club_flg)
VALUES (1002, 11, '摄影社', 'N');
INSERT INTO StudentClub (std_id, club_id, club_name, main_club_flg)
VALUES (1003, 12, '篮球俱乐部', 'N');
INSERT INTO StudentClub (std_id, club_id, club_name, main_club_flg)
VALUES (1003, 13, '足球俱乐部', 'Y');
INSERT INTO StudentClub (std_id, club_id, club_name, main_club_flg)
VALUES (1004, 14, '围棋', 'N');

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
FROM StudentClub 
GROUP BY std_id;

总结

  • 作为表达式,CASE表达式在执行时会被判定为一个固定值,因此它可以写在聚合函数内部;也正因为它是表达式,所以还可以写在SELECE子句,GROUP BY子句,WHERE子句,ORDER BY子句里.简单点说,在能写列名和常量的地方,通常都可以写CASE表达式.

练习题

  • 练习题 1-1-1: 多列数据的最大值

    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
    create table greatests (
      `key` varchar(20) not null ,
      x int ,
      y int ,
      z int ,
      PRIMARY key(`key`)
    );
    insert into greatests (`key`,x,y,z) values('A',1,2,3);
    insert into greatests (`key`,x,y,z) values('B',5,5,2);
    insert into greatests (`key`,x,y,z) values('C',4,7,1);
    insert into greatests (`key`,x,y,z) values('D',3,3,8);

    -- 答案
    SELECT
    	`key`,
    CASE
    	WHEN x > y THEN
    	( CASE WHEN x > z THEN x ELSE z END ) ELSE ( CASE WHEN y > z THEN y ELSE z END ) 
    	END AS GREATEST
    FROM greatests;

    +-----+----------+
    | key | GREATEST |
    +-----+----------+
    | A   |        3 |
    | B   |        5 |
    | C   |        7 |
    | D   |        8 |
    +-----+----------+

  • 练习题1-1-2: 转换行列——在表头里加入汇总和再揭

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
REATE TABLE PopTbl2
(pref_name VARCHAR(32),
 sex CHAR(1) NOT NULL,
 population INTEGER NOT NULL,
    PRIMARY KEY(pref_name, 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);


SELECT 
			 CASE 
				WHEN sex = '1' THEN '男'
				WHEN sex = '2' THEN '女'
				ELSE sex
			 END AS '性别',
       SUM(population) AS '全国',
       SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS '德岛',
       SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS '香川',
       SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS '爱媛',
       SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS '高知',
       SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知')
                THEN population ELSE 0 END) AS '四国(再揭)'
 FROM PopTbl2
 GROUP BY sex;
 
-- 输出
 +-----+-----+-----+-----+-----+-----+-----------+
| 性别 | 全国 | 德岛 | 香川 | 爱媛 | 高知 | 四国(再揭) |
+-----+-----+-----+-----+-----+-----+-----------+
| 男  | 855 | 60  | 100 | 100 | 100 | 360       |
| 女  | 845 | 40  | 100 | 50  | 100 | 290       |
+-----+-----+-----+-----+-----+-----+-----------+

  • 练习题 1-1-3: 用ORDER BY生成“排序”列

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT key,
             CASE key
               WHEN 'B' THEN 1
               WHEN 'A' THEN 2
               WHEN 'D' THEN 3
               WHEN 'C' THEN 4
             ELSE NULL END AS sort_col
    FROM greatests
    ORDER BY sort_col;