详解Mysql case then使用

Phemia ·

更新时间:2024-11-10

· 929 次阅读

表的创建


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 YORN
FROM lee

第二种用法：


SELECT NAME, CASE name
 WHEN 'sam' THEN 'yong'
 WHEN 'lee' THEN 'handsome'
 ELSE 'good' END as oldname
FROM lee

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


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

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


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

第二题:统计出属于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

您可能感兴趣的文章:MySQL中使用case when 语句实现多条件查询的方法SQL集合函数中case when then 使用技巧MySQL 的CASE WHEN 语句使用说明mysql中IFNULL,IF,CASE的区别介绍sum(case when then)(判断男女生的个数)MYSQL中有关SUM字段按条件统计使用IF函数(case)问题MySQL的CASE WHEN语句的几个使用实例sql学习之CASE WHEN THEN ELSE END的用法MySQL中的if和case语句使用总结

THEN case Mysql

1024 个赞