详解Mysqlcasethen使用

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

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

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

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

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'

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