《Mysql實例詳解Mysql case then使用》要點：
本文介紹了Mysql實例詳解Mysql case then使用，希望對您有用。如果有疑問，可以聯系我們。
MYSQL應用表的創建
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
MYSQL應用數據插入：
MYSQL應用
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');
MYSQL應用第一種用法:
MYSQL應用
SELECT name,
CASE WHEN birthday < '1981' THEN 'old'
WHEN birthday > '1988' THEN 'yong'
ELSE 'ok' END YORN
FROM lee
MYSQL應用第二種用法：
MYSQL應用
SELECT NAME, CASE name
WHEN 'sam' THEN 'yong'
WHEN 'lee' THEN 'handsome'
ELSE 'good' END as oldname
FROM lee
MYSQL應用第三種：當然了,case when 語句還可以復合
MYSQL應用
select name, birthday,
case
when birthday > '1983' then 'yong'
when name='lee' then 'handsome'
else 'just so so' end
from lee;
MYSQL應用在這里用sql語句進行日期比較的話,需要對年加引號,要不然可能結果和預期的結果不同,
當然也可以用year函數來實現
MYSQL應用
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);
MYSQL應用第一題：對罰款登記分為三類,第一類low,包括大于0小于等于40的罰款,第二類moderate大于40到80之間的罰款,第三類high包含所有大于80的罰款
MYSQL應用
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
MYSQL應用第二題:統計出屬于low的罰款編號
MYSQL應用
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'
MYSQL應用PS：Mysql,Case When,Case多個字段
MYSQL應用
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
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/3687.html
