代码片段(4)
[代码] 使用CASE WHEN进行字符串替换处理
03 |
mysql> select * from sales; |
04 |
+-----+------------+--------+--------+--------+------+------------+ |
05 |
| num | name | winter | spring | summer | fall | category | |
06 |
+-----+------------+--------+--------+--------+------+------------+ |
07 |
| 1 | Java | 1067 | 200 | 150 | 267 | Holiday | |
08 |
| 2 | C | 970 | 770 | 531 | 486 | Profession | |
09 |
| 3 | JavaScript | 53 | 13 | 21 | 856 | Literary | |
10 |
| 4 | SQL | 782 | 357 | 168 | 250 | Profession | |
11 |
| 5 | Oracle | 589 | 795 | 367 | 284 | Holiday | |
12 |
| 6 | MySQL | 953 | 582 | 336 | 489 | Literary | |
13 |
| 7 | Cplus | 752 | 657 | 259 | 478 | Literary | |
14 |
| 8 | Python | 67 | 23 | 83 | 543 | Holiday | |
15 |
| 9 | PHP | 673 | 48 | 625 | 52 | Profession | |
16 |
+-----+------------+--------+--------+--------+------+------------+ |
17 |
9 rows in set (0.01 sec) |
19 |
mysql> SELECT name AS Name, |
21 |
-> WHEN "Holiday" THEN "Seasonal" |
22 |
-> WHEN "Profession" THEN "Bi_annual" |
23 |
-> WHEN "Literary" THEN "Random" END AS "Pattern" |
25 |
+------------+-----------+ |
27 |
+------------+-----------+ |
30 |
| JavaScript | Random | |
37 |
+------------+-----------+ |
38 |
9 rows in set (0.00 sec) |
45 |
num MEDIUMINT NOT NULL AUTO_INCREMENT, |
56 |
insert into sales value(1, 'Java', 1067 , 200, 150, 267,'Holiday'); |
57 |
insert into sales value(2, 'C',970,770,531,486,'Profession'); |
58 |
insert into sales value(3, 'JavaScript',53,13,21,856,'Literary'); |
59 |
insert into sales value(4, 'SQL',782,357,168,250,'Profession'); |
60 |
insert into sales value(5, 'Oracle',589,795,367,284,'Holiday'); |
61 |
insert into sales value(6, 'MySQL',953,582,336,489,'Literary'); |
62 |
insert into sales value(7, 'Cplus',752,657,259,478,'Literary'); |
63 |
insert into sales value(8, 'Python',67,23,83,543,'Holiday'); |
64 |
insert into sales value(9, 'PHP',673,48,625,52,'Profession'); |
71 |
WHEN "Holiday" THEN "Seasonal" |
72 |
WHEN "Profession" THEN "Bi_annual" |
73 |
WHEN "Literary" THEN "Random" END AS "Pattern" |
[代码] 简单语句
1 |
SELECT CASE WHEN 10*2=30 THEN '30 correct' |
2 |
WHEN 10*2=40 THEN '40 correct' |
3 |
ELSE 'Should be 10*2=20' |
[代码] 多重表达式
2 |
WHEN 20 THEN '20 correct' |
3 |
WHEN 30 THEN '30 correct' |
4 |
WHEN 40 THEN '40 correct' |
[代码] 在SELECT查询中使用CASE WHEN
02 |
mysql> SELECT Name, RatingID AS Rating, |
04 |
-> WHEN 'R' THEN 'Under 17 requires an adult.' |
05 |
-> WHEN 'X' THEN 'No one 17 and under.' |
06 |
-> WHEN 'NR' THEN 'Use discretion when renting.' |
07 |
-> ELSE 'OK to rent to minors.' |
11 |
+-----------+--------+------------------------------+ |
12 |
| Name | Rating | Policy | |
13 |
+-----------+--------+------------------------------+ |
14 |
| Africa | PG | OK to rent to minors. | |
15 |
| Amadeus | PG | OK to rent to minors. | |
16 |
| Christmas | NR | Use discretion when renting. | |
17 |
| Doc | G | OK to rent to minors. | |
18 |
| Falcon | NR | Use discretion when renting. | |
19 |
| Mash | R | Under 17 requires an adult. | |
20 |
| Show | NR | Use discretion when renting. | |
21 |
| View | NR | Use discretion when renting. | |
22 |
+-----------+--------+------------------------------+ |
23 |
8 rows in set (0.01 sec) |
31 |
ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, |
32 |
Name VARCHAR(60) NOT NULL, |
33 |
NumDisks TINYINT NOT NULL DEFAULT 1, |
34 |
RatingID VARCHAR(4) NOT NULL, |
35 |
StatID CHAR(3) NOT NULL |
39 |
INSERT INTO DVDs (Name, NumDisks, RatingID, StatID) |
40 |
VALUES ('Christmas', 1, 'NR', 's1'), |
41 |
('Doc', 1, 'G', 's2'), |
42 |
('Africa', 1, 'PG', 's1'), |
43 |
('Falcon', 1, 'NR', 's2'), |
44 |
('Amadeus', 1, 'PG', 's2'), |
45 |
('Show', 2, 'NR', 's2'), |
46 |
('View', 1, 'NR', 's1'), |
(责任编辑:IT) |