详解Mysql case then使用_Mysql

详解Mysql case then使用

2020-05-29 16:17简单--生活 Mysql

mysql case then在程序开发中经常用到，通过本文给大家介绍mysql case then使用相关知识，对mysql case then相关知识感兴趣的朋友一起学习吧

表的创建

				?

									CREATE 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

数据插入：

				?

									insert 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');

第一种用法:

				?

									SELECT name,

									 CASE WHEN birthday < '1981' THEN 'old'

									WHEN birthday > '1988' THEN 'yong'

									 ELSE 'ok' END YORN

									FROM lee

第二种用法：

				?

									SELECT NAME, CASE name

									 WHEN 'sam' THEN 'yong'

									 WHEN 'lee' THEN 'handsome'

									 ELSE 'good' END as oldname

									FROM lee

第三种：当然了，case when 语句还可以复合

				?

									select name, birthday,

									 case

									when birthday > '1983' then 'yong'

									 when name='lee' then 'handsome'

									 else 'just so so' end

									from lee;

在这里用sql语句进行日期比较的话，需要对年加引号，要不然可能结果和预期的结果不同，
当然也可以用year函数来实现

				?

									select 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的罚款

				?

									select 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的罚款编号

				?

									select * 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多个字段

				?

									select 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