mysql中case when试题_MySQL_MySQL的CASE WHEN语句的几个使用实例，使用CASE WHEN进行字符串替换处 - phpStudy...

MySQL的CASE WHEN语句的几个使用实例

使用CASE WHEN进行字符串替换处理

mysql> select * from sales;

9 rows in set (0.01 sec)

SELECT name AS Name,

CASE category

WHEN "Holiday" THEN "Seasonal"

WHEN "Profession" THEN "Bi_annual"

WHEN "Literary" THEN "Random" END AS "Pattern"

FROM sales;

9 rows in set (0.00 sec)

CREATE TABLE sales(

num MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(20), winter INT, spring INT,

summer INT, fall INT, category CHAR(13), primary key(num)

) ENGINE=MyISAM;

insert into sales value(1, 'Java', 1067 , 200, 150, 267,'Holiday');

insert into sales value(2, 'C',970,770,531,486,'Profession');

insert into sales value(3, 'JavaScript',53,13,21,856,'Literary');

insert into sales value(4, 'SQL',782,357,168,250,'Profession');

insert into sales value(5, 'Oracle',589,795,367,284,'Holiday');

insert into sales value(6, 'MySQL',953,582,336,489,'Literary');

insert into sales value(7, 'Cplus',752,657,259,478,'Literary');

insert into sales value(8, 'Python',67,23,83,543,'Holiday');

insert into sales value(9, 'PHP',673,48,625,52,'Profession');

简单语句

SELECT CASE WHEN 10*2=30 THEN '30 correct'

WHEN 10*2=40 THEN '40 correct'

ELSE 'Should be 10*2=20'

END AS `result`;

多重表达式

SELECT CASE 10*2

WHEN 20 THEN '20 correct'

WHEN 30 THEN '30 correct'

WHEN 40 THEN '40 correct'

ELSE 'No results'

END AS `result`;

在SELECT查询中使用CASE WHEN

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;

8 rows in set (0.01 sec)相关阅读:

PHP设置头信息及取得返回头信息的方法

jquery实现隐藏在左侧的弹性弹出菜单效果

Android TextView跑马灯效果实现方法

win7旗舰版系统关闭U盘自动播放功能的图文设置教程

jQuery中:submit选择器用法实例

最强Win10美化工具Start10：秒变Win7

怎样让Windows XP自动登陆? XP系统自动登陆的几种方法

C#圆角窗体简单实现方法

PHP经典面试题之设计模式(经常遇到)

iOS App中UITableView左滑出现删除按钮及其cell的重用

Java比较两个List的值是否相等的方法

Appcrash是什么? Win7系统运行DNF游戏提示“Appcrash错误，无法运行”的三种解决方案

Java输入输出流复制文件所用时间对比

JS获取文本框,下拉框,单选框的值的简单实例