Fork me on GitHub

SQL 进阶( CASE 表达式 )

CASE 表达式有简单 CASE 表达式(simple case expression)和搜索 CASE 表达式(searched case expression)两种写法,它们分别如下所示:
1
2
3
4
5
6
7
8
9
10
11
-- 简单CASE表达式
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

-- 搜索CASE表达式
CASE
WHEN sex='1' THEN '男'
WHEN sex='2' THEN '女'
ELSE '其他' 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
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
/* 将已有编号方式转换为新的方式并统计 */
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);


/* 把县编号转换成地区编号(1) */
SELECT CASE pref_name
WHEN '德岛' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '爱媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福冈' THEN '九州'
WHEN '佐贺' THEN '九州'
WHEN '长崎' THEN '九州'
ELSE '其他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY CASE pref_name
WHEN '德岛' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '爱媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福冈' THEN '九州'
WHEN '佐贺' THEN '九州'
WHEN '长崎' THEN '九州'
ELSE '其他' END;

/* 按人口数量等级划分都道府县 */
SELECT CASE
WHEN population < 100 THEN '01'
WHEN population >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >= 300 THEN '04'
ELSE NULL END AS pop_class,
COUNT(*) AS cnt
FROM PopTbl
GROUP BY CASE
WHEN population < 100 THEN '01'
WHEN population >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >= 300 THEN '04'
ELSE NULL END;

/* 把县编号转换成地区编号(2):将CASE表达式归纳到一处 */
SELECT CASE pref_name
WHEN '德岛' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '爱媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福冈' THEN '九州'
WHEN '佐贺' THEN '九州'
WHEN '长崎' THEN '九州'
ELSE '其他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY district;

/* 用一条SQL语句进行不同条件的统计 */
CREATE 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 pref_name,
SUM(population) f
FROM PopTbl2
WHERE sex = '1'
GROUP BY pref_name;


-- 女性人口
SELECT pref_name,
SUM(population) f
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;


/* 员工工资信息表 */
CREATE TABLE Salaries
(
name VARCHAR(32) PRIMARY KEY,
salary INTEGER NOT NULL
);

INSERT INTO Salaries VALUES('相田', 300000);
INSERT INTO Salaries VALUES('神崎', 270000);
INSERT INTO Salaries VALUES('木村', 220000);
INSERT INTO Salaries VALUES('齐藤', 290000);

/* 用CHECK约束定义多个列的条件关系 */
/* 蕴含式

CONSTRAINT check_salary
CHECK
(CASE WHEN sex = '2'
THEN CASE WHEN salary <= 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1)
*/

/* 逻辑与

CONSTRAINT check_salary CHECK
( sex = '2' AND salary <= 200000 )

*/

/* 在 UPDATE 语句里进行条件分支 */
/* 错误示例 */

-- 条件 1
UPDATE Salaries
SET salary = salary * 0.9
WHERE salary >= 300000;


-- 条件 2
UPDATE Salaries
SET salary = salary * 1.2
WHERE salary >= 250000
AND salary < 280000;

UPDATE Salaries SET salary = 300000 WHERE name='相田';
UPDATE Salaries SET salary = 270000 WHERE name='神崎';

/* 用CASE表达式写正确的更新操作 */
UPDATE Salaries
SET salary = CASE
WHEN salary >= 300000
THEN salary * 0.9
WHEN salary >= 250000 AND salary < 280000
THEN salary * 1.2
ELSE salary END;

/* 在UPDATE语句里进行条件分支 */
CREATE TABLE SomeTable
(
p_key CHAR(1) PRIMARY KEY,
col_1 INTEGER NOT NULL,
col_2 CHAR(2) NOT NULL
);

INSERT INTO SomeTable VALUES('a', 1, '一');
INSERT INTO SomeTable VALUES('b', 2, '二');
INSERT INTO SomeTable VALUES('c', 3, '三');

/* 调换主键值*/
/* 三次 UPDATE 操作 */
UPDATE SomeTable SET p_key='d' where p_key='a';
UPDATE SomeTable SET p_key='a' where p_key='b';
UPDATE SomeTable SET p_key='b' where p_key='d';

/* 用CASE表达式调换主键值 */
/* 注意:在 PostgreSQL 和 MySQL 会因主键重复而出现错误 */
UPDATE SomeTable
SET p_key = CASE
WHEN p_key = 'a'
THEN 'b'
WHEN p_key = 'b'
THEN 'a'
ELSE p_key END
WHERE p_key IN ('a', 'b');



/* 表之间的数据匹配 */
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);

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

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

/* 在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 : 选择只加入了一个社团的学生
SELECT std_id, MAX(club_id) AS main_club
FROM StudentClub
GROUP BY std_id
HAVING COUNT(*) = 1;


-- 条件 2 : 选择加入了多个社团的学生
SELECT std_id, club_id AS main_club
FROM StudentClub
WHERE main_club_flg = 'Y';

/* 在CASE表达式中使用聚合函数 */
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;

/* 练习题1-1:多列数据的最大值(练习题1-1-3也会用到) */
CREATE TABLE Greatests
(
`key` 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);

/* 求 x 和 y 二者中较大的值 */

/* 仅适用于Oracle和MySQL */
SELECT `key`, GREATEST(x, y) as greatest
FROM Greatests;

SELECT `key`,
CASE
WHEN x >= y THEN x
ELSE y END AS greatest
FROM Greatests;

/* 求 x、y 和 z 中的最大值 */
SELECT `key`, GREATEST(x, y, z) as greatest
FROM Greatests;

SELECT `key`, GREATEST(GREATEST(x, y), z) AS greatest
FROM Greatests;

SELECT `key`,
CASE
WHEN x >= y and x >= z THEN x
WHEN y >= x and y >= z THEN y
WHEN z >= x and z >= y THEN z
ELSE NULL END AS greatest
FROM Greatests;

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;

/* 扩展至更多列,先进行行列转换,然后使用 MAX 函数来求解 */
SELECT `key`, MAX(col) AS greatest
FROM (SELECT `key`, x AS col
FROM Greatests
UNION ALL
SELECT `key`, y AS col
FROM Greatests
UNION ALL
SELECT `key`, z AS col
FROM Greatests) TMP
GROUP BY `key`;


/* 转换行列 —— 在表头里加入汇总和再揭(p.287) */
SELECT sex,
SUM(population) AS total,
SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS col_1,
SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS col_2,
SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS col_3,
SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS col_4,
SUM(CASE
WHEN pref_name IN ('德岛', '香川', '爱媛', '高知')
THEN population
ELSE 0 END) AS zaijie
FROM PopTbl2
GROUP BY sex;


/* 用 ORDER BY 生成 “排序” 列 */
SELECT `key`
FROM Greatests
ORDER BY CASE `key`
WHEN 'B' THEN 1
WHEN 'A' THEN 2
WHEN 'D' THEN 3
WHEN 'C' THEN 4
ELSE NULL END;

/* 把 “排序” 列也包括在结果中(p.288) */
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;
源码下载
坚持原创技术分享,您的支持将鼓励我继续创作!