详解Mysql case then使用
程序员文章站
2024-02-24 21:33:04
表的创建
create table `lee` (
`id` int(10) not null auto_increment,
`name` char(20...
表的创建
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