表的创建
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_then
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件!
如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
白云城资源网 Copyright www.dyhadc.com
暂无“详解Mysql case then使用”评论...
更新日志
2024年07月08日
2024年07月08日
- 孙露《不让我的眼泪陪我过夜》HQCDII 限量版 [WAV+CUE][517M]
- dnf巴卡尔武器怎么融合
- 魔兽世界wlk最强职业是什么 wlk最吃香最强职业推荐
- dnf巴卡尼单人和多人区别
- 群星《大热唱片DSD》APE+CUE
- 草蜢2012《软硬草蜢》3CD[WAV+CUE整轨]
- 雨林唱片发烧大碟-陈果《明知故犯HQCD》[WAV+CUE]
- 群星.2024-狐妖小红娘月红篇电视剧原声带【恒星引力】【FLAC分轨】
- 许美静.1998-好美静【上华】【WAV+CUE】
- 谭咏麟.1996-独一无二【宝丽金】【WAV+CUE】
- dnf平民怎么弄宠物
- dnf弓箭手职业
- dnf强化基础精通适合哪些职业
- V.A中国管弦乐纪念名盘《乡之音》FLAC
- [雨果]赵鹏《赵鹏·LPCD45》LPCD45[WAV+CUE]