mysql__CASE WHEN进行字符串替换处理

2019独角兽企业重金招聘Python工程师标准>>>

使用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;

转载于:https://my.oschina.net/u/1244264/blog/150659