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_annual23 -> 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 seasonal72 when profession then bi_annual73 when literary then random end as pattern74 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