mysql when case 条件判断语名用法

一个CASE表达式的默认返回值类型是任何返回值的相容集合类型,但具体情况视其所在语境而定,如果用在字符串语境中,则返回结果味字符串,如果用在数字语境中,则返回结果为十进制值、实值或整数值.

语法,代码如下:

1. CASEvalueWHEN[compare-value]THENresult[WHEN[compare-value]THENresult...][ELSEresult]ENDCASEWHEN[condition]THENresult[WHEN[condition]THENresult...][ELSEresult]END

实例,代码如下:

1. SELECTCASEWHEN10*2=30THEN'30correct'
2. WHEN10*2=40THEN'40correct'
3. ELSE'Shouldbe10*2=20'
4. END;

复杂点,代码如下:

1. SELECTCASE10*2
2. WHEN20THEN'20correct'
3. WHEN30THEN'30correct'
4. WHEN40THEN'40correct'
5. END;

实例,代码如下:

1. /*
2. mysql>SELECTName,RatingIDASRating,
3. ->CASERatingID
4. ->WHEN'R'THEN'Under17requiresanadult.'
5. ->WHEN'X'THEN'Noone17andunder.'
6. ->WHEN'NR'THEN'Usediscretionwhenrenting.'
7. ->ELSE'OKtorenttominors.'
8. ->ENDASPolicy
9. ->FROMDVDs
10. ->ORDERBYName;
11. +-----------+--------+------------------------------+
12. |Name|Rating|Policy|
13. +-----------+--------+------------------------------+
14. |Africa|PG|OKtorenttominors.|
15. |Amadeus|PG|OKtorenttominors.|
16. |Christmas|NR|Usediscretionwhenrenting.|
17. |Doc|G|OKtorenttominors.|
18. |Falcon|NR|Usediscretionwhenrenting.|
19. |Mash|R|Under17requiresanadult.|
20. |Show|NR|Usediscretionwhenrenting.|
21. |View|NR|Usediscretionwhenrenting.|
22. +-----------+--------+------------------------------+
23. 8rowsinset(0.01sec)
24. */
25. DroptableDVDs;
26. CREATETABLEDVDs(
27. IDSMALLINTNOTNULLAUTO_INCREMENTPRIMARYKEY,
28. NameVARCHAR(60)NOTNULL,
29. NumDisksTINYINTNOTNULLDEFAULT1,
30. RatingIDVARCHAR(4)NOTNULL,
31. StatIDCHAR(3)NOTNULL
32. )
33. ENGINE=INNODB;
34. INSERTINTODVDs(Name,NumDisks,RatingID,StatID)
35. VALUES('Christmas',1,'NR','s1'),
36. ('Doc',1,'G','s2'),
37. ('Africa',1,'PG','s1'),
38. ('Falcon',1,'NR','s2'),
39. ('Amadeus',1,'PG','s2'),
40. ('Show',2,'NR','s2'),
41. ('View',1,'NR','s1'),
42. ('Mash',2,'R','s2');
43. SELECTName,RatingIDASRating,
44. CASERatingID
45. WHEN'R'THEN'Under17requiresanadult.'
46. WHEN'X'THEN'Noone17andunder.'
47. WHEN'NR'THEN'Usediscretionwhenrenting.'
48. ELSE'OKtorenttominors.'--phpfensi.com
49. ENDASPolicy
50. FROMDVDs
51. ORDERBYName;