mysql case字符串_MySQL 的 CASE WHEN 语句 进行字符串替换处理

/*

mysql> select * from sales;

+-----+------------+--------+--------+--------+------+------------+

| num | name | winter | spring | summer | fall | category |

+-----+------------+--------+--------+--------+------+------------+

| 1 | Java | 1067 | 200 | 150 | 267 | Holiday |

| 2 | C | 970 | 770 | 531 | 486 | Profession |

| 3 | JavaScript | 53 | 13 | 21 | 856 | Literary |

| 4 | SQL | 782 | 357 | 168 | 250 | Profession |

| 5 | Oracle | 589 | 795 | 367 | 284 | Holiday |

| 6 | MySQL | 953 | 582 | 336 | 489 | Literary |

| 7 | Cplus | 752 | 657 | 259 | 478 | Literary |

| 8 | Python | 67 | 23 | 83 | 543 | Holiday |

| 9 | PHP | 673 | 48 | 625 | 52 | Profession |

+-----+------------+--------+--------+--------+------+------------+

9 rows in set (0.01 sec)

mysql> SELECT name AS Name,

-> CASE category

-> WHEN "Holiday" THEN "Seasonal"

-> WHEN "Profession" THEN "Bi_annual"

-> WHEN "Literary" THEN "Random" END AS "Pattern"

-> FROM sales;

+------------+-----------+

| Name | Pattern |

+------------+-----------+

| Java | Seasonal |

| C | Bi_annual |

| JavaScript | Random |

| SQL | Bi_annual |

| Oracle | Seasonal |

| MySQL | Random |

| Cplus | Random |

| Python | Seasonal |

| PHP | Bi_annual |

+------------+-----------+

9 rows in set (0.00 sec)

*/

Drop table sales;

CREATE TABLE sales(

num MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(20),

winter INT,

spring INT,

summer INT,

fall INT,

category CHAR(13),

primary key(num)

)type=MyISAM;

insert into sales value(1, 'Java', 1067 , 200, 150, 267,'Holiday');

insert into sales value(2, 'C',970,770,531,486,'Profession');

insert into sales value(3, 'JavaScript',53,13,21,856,'Literary');

insert into sales value(4, 'SQL',782,357,168,250,'Profession');

insert into sales value(5, 'Oracle',589,795,367,284,'Holiday');

insert into sales value(6, 'MySQL',953,582,336,489,'Literary');

insert into sales value(7, 'Cplus',752,657,259,478,'Literary');

insert into sales value(8, 'Python',67,23,83,543,'Holiday');

insert into sales value(9, 'PHP',673,48,625,52,'Profession');

select * from sales;

SELECT name AS Name,

CASE category

WHEN "Holiday" THEN "Seasonal"

WHEN "Profession" THEN "Bi_annual"

WHEN "Literary" THEN "Random" END AS "Pattern"

FROM sales;

分享到：

2011-05-11 23:03

浏览 672

评论