bitsCN.com
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 category21 -> WHEN "Holiday" THEN "Seasonal"//把sales表字段category中Holiday值替换为seasonal22 -> WHEN "Profession" THEN "Bi_annual"23 -> WHEN "Literary" THEN "Random" END AS "Pattern"//查询的结果命名为一个新的字段为pattern24 -> 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 category71 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 categorywhen "Holiday" then "1111" #把categroy字段中的Holiday替换为1111WHEN "Profession" THEN "2222" #把categroy字段中的Holiday替换为2222WHEN "Literary" THEN "3333" #把categroy字段中的Holiday替换为3333END AS "从新命名标题" #把categroy重新命名为'从新命名标题'FROM sales;
bitsCN.com
