mysql__CASE WHEN進行字符串替換處理 使用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)
18
19 mysql> SELECT name AS Name,
20 -> CASE category
21
-> WHEN "Holiday" THEN "Seasonal"//把sales表字段category中Holiday值替換為seasonal
22 -> WHEN "Profession" THEN "Bi_annual"
23 -> WHEN "Literary" THEN "Random" END AS "Pattern"//查詢的結果命名為一個新的字段為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;
SELECT num,name AS Name, case category
when "Holiday" then "1111" #把categroy字段中的Holiday替換為1111
WHEN "Profession" THEN "2222" #把categroy字段中的Holiday替換為2222
WHEN "Literary" THEN "3333" #把categroy字段中的Holiday替換為3333
END AS "從新命名標題" #把categroy重新命名為'從新命名標題'
FROM sales;
