MySQL 的 CASE WHEN 语句
代码片段(4)
[代码] 使用CASE WHEN进行字符串替换处理
01 |
/* |
02 |
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) |
18 |
19 |
mysql> SELECT name AS Name, |
20 |
-> CASE category |
21 |
-> WHEN "Holiday" THEN "Seasonal" |
22 |
-> WHEN "Profession" THEN "Bi_annual" |
23 |
-> WHEN "Literary" THEN "Random" END AS "Pattern" |
24 |
-> FROM sales; |
25 |
+------------+-----------+ |
26 |
| Name | Pattern | |
27 |
+------------+-----------+ |
28 |
| Java | Seasonal | |
29 |
| C | Bi_annual | |
30 |
| JavaScript | Random | |
31 |
| SQL | Bi_annual | |
32 |
| Oracle | Seasonal | |
33 |
| MySQL | Random | |
34 |
| Cplus | Random | |
35 |
| Python | Seasonal | |
36 |
| PHP | Bi_annual | |
37 |
+------------+-----------+ |
38 |
9 rows in set (0.00 sec) |
39 |
40 |
41 |
*/ |
42 |
Drop table sales; |
43 |
|
44 |
CREATE TABLE sales( |
45 |
num MEDIUMINT NOT NULL AUTO_INCREMENT, |
46 |
name CHAR(20), |
47 |
winter INT, |
48 |
spring INT, |
49 |
summer INT, |
50 |
fall INT, |
51 |
category CHAR(13), |
52 |
primary key(num) |
53 |
)type=MyISAM; |
54 |
55 |
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'); |
65 |
66 |
select * from sales; |
67 |
68 |
69 |
SELECT name AS Name, |
70 |
CASE category |
71 |
WHEN "Holiday" THEN "Seasonal" |
72 |
WHEN "Profession" THEN "Bi_annual" |
73 |
WHEN "Literary" THEN "Random" END AS "Pattern" |
74 |
FROM sales; |
[代码] 简单语句
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' |
4 |
END; |
[代码] 多重表达式
1 |
SELECT CASE 10*2 |
2 |
WHEN 20 THEN '20 correct' |
3 |
WHEN 30 THEN '30 correct' |
4 |
WHEN 40 THEN '40 correct' |
5 |
END; |
[代码] 在SELECT查询中使用CASE WHEN
01 |
/* |
02 |
mysql> SELECT Name, RatingID AS Rating, |
03 |
-> CASE RatingID |
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.' |
08 |
-> END AS Policy |
09 |
-> FROM DVDs |
10 |
-> ORDER BY Name; |
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) |
24 |
25 |
26 |
*/ |
27 |
28 |
Drop table DVDs; |
29 |
30 |
CREATE TABLE DVDs ( |
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 |
36 |
) |
37 |
ENGINE=INNODB; |
38 |
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'), |
47 |
('Mash', 2, 'R', 's2'); |
48 |
|
49 |
50 |
SELECT Name, RatingID AS Rating, |
51 |
CASE RatingID |
52 |
WHEN 'R' THEN 'Under 17 requires an adult.' |
53 |
WHEN 'X' THEN 'No one 17 and under.' |
54 |
WHEN 'NR' THEN 'Use discretion when renting.' |
55 |
ELSE 'OK to rent to minors.' |
56 |
END AS Policy |
57 |
FROM DVDs |
58 |
ORDER BY Name; |
