详解Mysql case then使用_MySQL

详解Mysql case then使用_MySQL

表的创建

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 YORNFROM lee

第二种用法：

SELECT NAME, CASE name WHEN 'sam' THEN 'yong' WHEN 'lee' THEN 'handsome' ELSE 'good' END as oldnameFROM le<span>本文来源gaodai#ma#com搞*!代#%^码$网*</span>e

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

select name, birthday, case when birthday > '1983' then 'yong' when name='lee' then 'handsome' else 'just so so' endfrom lee;

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

select name, case when year(birthday) > 1988 then 'yong' when year(birthday) < 1980 then 'old' else 'ok' ENDfrom 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' ENDFROM 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 pwhere 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

以上就是详解Mysql case then使用_MySQL的内容，更多相关内容请随时关注我们网站！

喜欢 (0)赏

[搞代码]

分享 (0)