详解Mysql case then使用_MySQL-tutoriel mysql-php.cn

详解Mysql case then使用_MySQL

PHP中文网

Libérer： 2016-05-27 13:46:19

original

1472 Les gens l'ont consulté

详解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

Copier après la connexion

数据插入：

insert into lee(name,birthday) values (&#39;sam&#39;,&#39;1990-01-01&#39;);
insert into lee(name,birthday) values (&#39;lee&#39;,&#39;1980-01-01&#39;);
insert into lee(name,birthday) values (&#39;john&#39;,&#39;1985-01-01&#39;);

Copier après la connexion

第一种用法:

SELECT name,
 CASE WHEN birthday < &#39;1981&#39; THEN &#39;old&#39; 
WHEN birthday > &#39;1988&#39; THEN &#39;yong&#39;
 ELSE &#39;ok&#39; END YORN
FROM lee

Copier après la connexion

第二种用法：

SELECT NAME, CASE name
 WHEN &#39;sam&#39; THEN &#39;yong&#39;
 WHEN &#39;lee&#39; THEN &#39;handsome&#39;
 ELSE &#39;good&#39; END as oldname
FROM lee

Copier après la connexion

第三种：当然了，case when 语句还可以复合

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

Copier après la connexion

在这里用sql语句进行日期比较的话，需要对年加引号，要不然可能结果和预期的结果不同，
当然也可以用year函数来实现

select name,
 case when year(birthday) > 1988 then &#39;yong&#39;
 when year(birthday) < 1980 then &#39;old&#39;
 else &#39;ok&#39; 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,&#39;2008-01-01&#39;,3.45);
insert into penalties values(2,&#39;2009-01-01&#39;,50.45);
insert into penalties values(3,&#39;2008-07-01&#39;,80.45);

Copier après la connexion

第一题：对罚款登记分为三类，第一类low,包括大于0小于等于40的罚款，第二类moderate大于40到80之间的罚款，第三类high包含所有大于80的罚款

select payment_date, amount,
 case 
when amount >= 0 AND amount < 40 then &#39;low&#39;
 when amount >=40 AND amount < 80 then &#39;moderate&#39;
 when amount >=80 then &#39;high&#39; 
else &#39;null&#39; END
FROM penalties

Copier après la connexion

第二题:统计出属于low的罚款编号

select * from 
( select paymentno, amount,
 case 
when amount >= 0 AND amount < 40 then &#39;low&#39;
 when amount >=40 AND amount < 80 then &#39;moderate&#39;
 when amount >=80 then &#39;high&#39; 
else &#39;incorrect&#39; end lvl
 from penalties) as p
where p.lvl = &#39;low&#39;

Copier après la connexion

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,&#39;%Y-%m-%d&#39;) as RegistDate, 
 case when b.usedstartTime is not null and b.UsedEndTime is null then &#39;1&#39;
when b.usedstartTime is not null and b.UsedEndTime is not null then &#39;2&#39; 
 end as &#39;usedState&#39;
 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

Copier après la connexion

以上就是详解Mysql case then使用_MySQL的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！