快捷導(dǎo)航

詳解Mysql case then使用

更新時間：2015年12月25日 15:38:57 作者：簡單--生活

mysql case then在程序開發(fā)中經(jīng)常用到，通過本文給大家介紹mysql case then使用相關(guān)知識，對mysql case then相關(guān)知識感興趣的朋友一起學(xué)習(xí)吧

表的創(chuàng)建

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

數(shù)據(jù)插入：

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

第三種：當(dāng)然了，case when 語句還可以復(fù)合

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

在這里用sql語句進(jìn)行日期比較的話，需要對年加引號，要不然可能結(jié)果和預(yù)期的結(jié)果不同，
當(dāng)然也可以用year函數(shù)來實現(xiàn)

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

第二題:統(tǒng)計出屬于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

您可能感興趣的文章: