各位用户为了找寻关于详解Mysql case then使用的资料费劲了很多周折。这里教程网为您整理了关于详解Mysql case then使用的相关资料，仅供查阅，以下为您介绍关于详解Mysql case then使用的详细内容
表的创建
? 1 2 3 4 5CREATE TABLE `lee` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` char(20) DEFAULT NULL,
`birthday` datetime DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
数据插入：
? 1 2 3insert into lee(name,birthday) values ('sam','1990-01-01');
insert into lee(name,birthday) values ('lee','1980-01-01');
insert into lee(name,birthday) values ('john','1985-01-01');
第一种用法:
? 1 2 3 4 5SELECT name,
CASE WHEN birthday < '1981' THEN 'old'
WHEN birthday > '1988' THEN 'yong'
ELSE 'ok' END YORN
FROM lee
第二种用法：
? 1 2 3 4 5SELECT NAME, CASE name
WHEN 'sam' THEN 'yong'
WHEN 'lee' THEN 'handsome'
ELSE 'good' END as oldname
FROM lee
第三种：当然了，case when 语句还可以复合
? 1 2 3 4 5 6select name, birthday,
case
when birthday > '1983' then 'yong'
when name='lee' then 'handsome'
else 'just so so' end
from lee;
在这里用sql语句进行日期比较的话，需要对年加引号，要不然可能结果和预期的结果不同， 当然也可以用year函数来实现
? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16select name,
case when year(birthday) > 1988 then 'yong'
when year(birthday) < 1980 then 'old'
else 'ok' END
from lee;
==========================================================
create table penalties
(
paymentno INTEGER not NULL,
payment_date DATE not null,
amount DECIMAL(7,2) not null,
primary key(paymentno)
)
insert into penalties values(1,'2008-01-01',3.45);
insert into penalties values(2,'2009-01-01',50.45);
insert into penalties values(3,'2008-07-01',80.45);
第一题：对罚款登记分为三类，第一类low,包括大于0小于等于40的罚款，第二类moderate大于40到80之间的罚款，第三类high包含所有大于80的罚款
? 1 2 3 4 5 6 7select payment_date, amount,
case
when amount >= 0 AND amount < 40 then 'low'
when amount >=40 AND amount < 80 then 'moderate'
when amount >=80 then 'high'
else 'null' END
FROM penalties
第二题:统计出属于low的罚款编号
? 1 2 3 4 5 6 7 8 9select * from
( select paymentno, amount,
case
when amount >= 0 AND amount < 40 then 'low'
when amount >=40 AND amount < 80 then 'moderate'
when amount >=80 then 'high'
else 'incorrect' end lvl
from penalties) as p
where p.lvl = 'low'
PS：Mysql，Case When,Case多个字段
? 1 2 3 4 5 6 7 8 9select distinct a.PatientID,a.PatientCode,a.PatientSex,a.MobileNo,a.HomePhoneNo,a.UserAge,a.PatientName,a.PatientIDCard, DATE_FORMAT(a.RegistDate,'%Y-%m-%d') as RegistDate,
case when b.usedstartTime is not null and b.UsedEndTime is null then '1'
when b.usedstartTime is not null and b.UsedEndTime is not null then '2'
end as 'usedState'
from mets_v_patient_baseinfo a
left join mets_devices_used_history b on a.patientid = b.PatientID
where (select ifnull(IsDeleted,0) from userpublic_info where UserID = a.PatientID ) = 0
and 1=1
order by PatientID Desc limit 0,15