mysql when case 条件判断语名用法

语法

代码如下	复制代码
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

实例

代码如下	复制代码
SELECT CASE WHEN 102=30 THEN '30 correct' WHEN 102=40 THEN '40 correct' ELSE 'Should be 10*2=20' END;

复杂点

代码如下	复制代码
SELECT CASE 10*2 WHEN 20 THEN '20 correct' WHEN 30 THEN '30 correct' WHEN 40 THEN '40 correct' END;

实例

代码如下

复制代码

/*
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 ('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,
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;

时间： 2024-10-21 07:50:59

mysql when case 条件判断语名用法

mysql when case 条件判断语名用法的相关文章

mysql中case 和if的用法

mysql中模糊查询的四种用法介绍_Mysql

mysql多个条件分列显示问题

mysql中模糊查询的四种用法介绍

大小写-EF和mysql做关系映射，表名全部小写了，怎么解决？

mysql 在一定条件下一个家庭id一样的语句怎么写

mysql数据同步：不同数据库名，不同表名，字段名也不相同，怎么同步数据呢？

数据库查询判断-vb2012 查询mysql数据库 where 条件语句有多个变量该怎么写？

mysql中replace、regexp正则表达式替换用法