詳解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 lee

第三種：當然了，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