鉴客 MySQL 的 CASE WHEN 语句

代码片段(4)

[代码] 使用CASE WHEN进行字符串替换处理

01

/*

02

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 rowsin set (0.01 sec)

18

19	mysql> SELECTname AS Name,

20	-> CASE category

21	-> WHEN "Holiday" THEN "Seasonal"

22	-> WHEN "Profession" THEN "Bi_annual"

23	-> WHEN "Literary" THEN "Random" ENDAS "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 rowsin set (0.00 sec)

39

40

41

*/

42	Drop tablesales;

43

44	CREATE TABLEsales(

45	num MEDIUMINTNOT NULL AUTO_INCREMENT,

46	nameCHAR(20),

47	winter INT,

48	spring INT,

49	summer INT,

50

fall INT,

51	category CHAR(13),

52	primarykey(num)

53	)type=MyISAM;

54

55

56	insert intosales value(1, 'Java', 1067 , 200, 150, 267,'Holiday');

57	insert intosales value(2, 'C',970,770,531,486,'Profession');

58	insert intosales value(3, 'JavaScript',53,13,21,856,'Literary');

59	insert intosales value(4, 'SQL',782,357,168,250,'Profession');

60	insert intosales value(5, 'Oracle',589,795,367,284,'Holiday');

61	insert intosales value(6, 'MySQL',953,582,336,489,'Literary');

62	insert intosales value(7, 'Cplus',752,657,259,478,'Literary');

63	insert intosales value(8, 'Python',67,23,83,543,'Holiday');

64	insert intosales value(9, 'PHP',673,48,625,52,'Profession');

65

66	select * from sales;

67

68

69	SELECT nameAS 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;

[代码] 简单语句

1	SELECT CASEWHEN 10*2=30 THEN'30 correct'

2	WHEN10*2=40 THEN '40 correct'

3	ELSE'Should be 10*2=20'

4

END;

[代码] 多重表达式

1	SELECT CASE10*2

2	WHEN20 THEN '20 correct'

3	WHEN30 THEN '30 correct'

4	WHEN40 THEN '40 correct'

5

END;

[代码] 在SELECT查询中使用CASE WHEN

01

/*

02	mysql> SELECTName, RatingID ASRating,

03	->    CASE RatingID

04	->       WHEN 'R' THEN 'Under 17 requires an adult.'

05	->       WHEN 'X' THEN 'No one 17 and under.'

06	->       WHEN 'NR' THEN 'Use discretion when renting.'

07	->       ELSE 'OK to rent to minors.'

08	->    END AS Policy

09	-> FROM DVDs

10	-> ORDER BY Name;

11	+-----------+--------+------------------------------+

12	| Name     | Rating | Policy                       |

13	+-----------+--------+------------------------------+

14	| Africa    | PG     | OK torent to minors.        |

15	| Amadeus   | PG     | OK torent to minors.        |

16	| Christmas | NR     | Use discretion when renting. |

17	| Doc       | G      | OK torent to minors.        |

18	| Falcon    | NR     | Use discretion when renting. |

19	| Mash      | R      | Under 17 requires an adult.  |

20	| Show      | NR     | Use discretion when renting. |

21	| View     | NR     | Use discretion whenrenting. |

22	+-----------+--------+------------------------------+

23	8 rowsin set (0.01 sec)

24

25

26

*/

27

28	Drop tableDVDs;

29

30	CREATE TABLEDVDs (

31	ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARYKEY,

32	NameVARCHAR(60) NOTNULL,

33	NumDisks TINYINTNOT NULL DEFAULT 1,

34	RatingID VARCHAR(4) NOTNULL,

35	StatID CHAR(3) NOTNULL

36

)

37	ENGINE=INNODB;

38

39	INSERT INTODVDs (Name, NumDisks, RatingID, StatID)

40	VALUES ('Christmas', 1,'NR', 's1'),

41	('Doc',       1,'G',  's2'),

42	('Africa',    1,'PG', 's1'),

43	('Falcon',    1,'NR', 's2'),

44	('Amadeus',   1,'PG', 's2'),

45	('Show',      2,'NR', 's2'),

46	('View',      1,'NR', 's1'),

47	('Mash',      2,'R',  's2');

48

49

50	SELECT Name, RatingIDAS Rating,

51	CASERatingID

52	WHEN'R' THEN 'Under 17 requires an adult.'

53	WHEN'X' THEN 'No one 17 and under.'

54	WHEN'NR' THEN 'Use discretion when renting.'

55	ELSE'OK to rent to minors.'

56	ENDAS Policy

57

FROM DVDs

58	ORDER BYName;