详解Mysql case then使用
表的创建
CREATETABLE`lee`( `id`int(10)NOTNULLAUTO_INCREMENT, `name`char(20)DEFAULTNULL, `birthday`datetimeDEFAULTNULL, PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8
数据插入：
insertintolee(name,birthday)values('sam','1990-01-01');
insertintolee(name,birthday)values('lee','1980-01-01');
insertintolee(name,birthday)values('john','1985-01-01');
第一种用法:
SELECTname, CASEWHENbirthday<'1981'THEN'old' WHENbirthday>'1988'THEN'yong' ELSE'ok'ENDYORN FROMlee
第二种用法：
SELECTNAME,CASEname WHEN'sam'THEN'yong' WHEN'lee'THEN'handsome' ELSE'good'ENDasoldname FROMlee
第三种：当然了，casewhen语句还可以复合
selectname,birthday, case whenbirthday>'1983'then'yong' whenname='lee'then'handsome' else'justsoso'end fromlee;
在这里用sql语句进行日期比较的话，需要对年加引号，要不然可能结果和预期的结果不同，
当然也可以用year函数来实现
selectname, casewhenyear(birthday)>1988then'yong' whenyear(birthday)<1980then'old' else'ok'END fromlee; ========================================================== createtablepenalties ( paymentnoINTEGERnotNULL, payment_dateDATEnotnull, amountDECIMAL(7,2)notnull, primarykey(paymentno) ) insertintopenaltiesvalues(1,'2008-01-01',3.45); insertintopenaltiesvalues(2,'2009-01-01',50.45); insertintopenaltiesvalues(3,'2008-07-01',80.45);
第一题：对罚款登记分为三类，第一类low,包括大于0小于等于40的罚款，第二类moderate大于40到80之间的罚款，第三类high包含所有大于80的罚款
selectpayment_date,amount, case whenamount>=0ANDamount<40then'low' whenamount>=40ANDamount<80then'moderate' whenamount>=80then'high' else'null'END FROMpenalties
第二题:统计出属于low的罚款编号
select*from (selectpaymentno,amount, case whenamount>=0ANDamount<40then'low' whenamount>=40ANDamount<80then'moderate' whenamount>=80then'high' else'incorrect'endlvl frompenalties)asp wherep.lvl='low'
PS：Mysql，CaseWhen,Case多个字段
selectdistincta.PatientID,a.PatientCode,a.PatientSex,a.MobileNo,a.HomePhoneNo,a.UserAge,a.PatientName,a.PatientIDCard,DATE_FORMAT(a.RegistDate,'%Y-%m-%d')asRegistDate, casewhenb.usedstartTimeisnotnullandb.UsedEndTimeisnullthen'1' whenb.usedstartTimeisnotnullandb.UsedEndTimeisnotnullthen'2' endas'usedState' frommets_v_patient_baseinfoa leftjoinmets_devices_used_historybona.patientid=b.PatientID where(selectifnull(IsDeleted,0)fromuserpublic_infowhereUserID=a.PatientID)=0 and1=1 orderbyPatientIDDesclimit0,15