sql 中的 case when(mysql 为例)
发布日期:2021-09-22 22:47:11 浏览次数:0 分类:技术文章
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 discretion when 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 TABLE DVDs (
ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR ( 60 ) NOT NULL,
NumDisks TINYINT NOT NULL DEFAULT 1 ,
RatingID VARCHAR ( 4 ) NOT NULL,
StatID CHAR ( 3 ) NOT NULL
)
ENGINE=INNODB;

INSERT INTO DVDs ( Name, NumDisks, RatingID, StatID )
VALUES ( 'Ch ristmas ', 1 , 'NR', 's1'),
( 'Do c ', 1 , 'G' , 's2'),
( 'Af rica ', 1 , 'PG', 's1'),
( 'Fa lcon ', 1 , 'NR', 's2'),
( 'Am adeus ', 1 , 'PG', 's2'),
( 'Sh ow ', 2 , 'NR', 's2'),
( 'Vi ew ', 1 , 'NR', 's1'),
( 'Ma sh ', 2 , 'R' , 's2');


SELECT Name, RatingID AS Rating,
CASE RatingID
WHEN 'R' THEN 'Un der 17 requires an adult. '
WHEN 'X' THEN 'No one 17 and under. '
WHEN 'NR' T HEN 'Us e discretion when renting. '
ELSE 'OK to rent to minors. '
END AS Policy
FROM DVDs
ORDER BY Name;
上一篇:java.lang.NumberFormatException 错误及解决办法
下一篇:java即时通信,推送技术详解