mysql__CASE WHEN进行字符串替换处理-白红宇

mysql__CASE WHEN进行字符串替换处理

阅读量：5981 次

发布时间：2019-06-20

本文共 2801 字，大约阅读时间需要 9 分钟。

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

你可能感兴趣的文章

Linux Namespace系列（09）：利用Namespace创建一个简单可用的容器

查看>>

博客搬家了

查看>>

Python中使用ElementTree解析xml

查看>>

linux的日志服务器关于屏蔽一些关键字的方法

[原][osgearth]osgearthviewer读取earth文件，代码解析（earth文件读取的一帧）

深入浅出JavaScript （五）详解Document.write()方法

查看>>

Beta冲刺——day6

查看>>

在一个程序中调用另一个程序并且传输数据到选择屏幕执行这个程序

查看>>

代码生成工具Database2Sharp中增加视图的代码生成以及主从表界面生成功能

查看>>

关于在VS2005中编写DLL遇到 C4251 警告的解决办法

查看>>