sql 中的 case when(mysql 为例）

最新推荐文章于 2024-02-25 09:00:00 发布

shiningHan

最新推荐文章于 2024-02-25 09:00:00 发布

阅读量570

点赞数

本文链接：https://blog.csdn.net/a962601835/article/details/8968507

版权

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;