sql 中的 case when(mysql 为例）

mysql> SELECT Name, RatingID AS Rating,
->CASE RatingID
->WHEN 'R' THEN 'Under 17 requires an adult.'
->WHEN 'X' THEN 'No one 17 and under.'
->WHEN 'NR' THEN 'Use discretionwhen renting.'
->ELSE 'OK to rent to minors.'
->END AS Policy
->FROM DVDs
->ORDER BY Name;
+-----------+--------+------------------------------+
| Name | Rating| Policy |
+-----------+--------+------------------------------+
| Africa | PG |OK to rent to minors. |
| Amadeus | PG |OK to rent to minors. |
| Christmas | NR |Use discretion when renting. |
| Doc | G | OK to rent to minors. |
| Falcon | NR |Use discretion when renting. |
| Mash | R | Under 17 requires an adult. |
| Show | NR | Use discretion when renting. |
| View | NR | Use discretion when renting. |
+-----------+--------+------------------------------+
8 rows in set (0.01 sec)


*/

Drop table DVDs;

CREATE TABLEDVDs (
ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
NameVARCHAR(60)NOT NULL,
NumDisksTINYINT NOT NULL DEFAULT 1,
RatingIDVARCHAR(4)NOT NULL,
StatIDCHAR(3)NOT NULL
)
ENGINE=INNODB;

INSERT INTODVDs (Name,NumDisks, RatingID, StatID)
VALUES ('Christmas',1, 'NR', 's1'),
('Doc',1,'G', 's2'),
('Africa',1,'PG', 's1'),
('Falcon',1,'NR', 's2'),
('Amadeus',1,'PG', 's2'),
('Show',2,'NR', 's2'),
('View',1,'NR', 's1'),
('Mash',2,'R', 's2');


SELECT Name,RatingID AS Rating,
CASERatingID
WHEN'R' THEN'Under17 requiresan adult.'
WHEN'X' THEN'No one17 andunder.'
WHEN'NR' THEN'Use discretion when renting.'
ELSE'OK torent to minors.'
ENDAS Policy
FROM DVDs
ORDER BY Name;