| 您的当前位置：首页 --> MYSQL教程 --> 详解Mysql case then使用 |
| MYSQL教程 详解Mysql case then使用 |
| 浏览次数:471 关键词 （ ） |
| 查看使用该CPU的产品 查看CPU天梯 |
| CPU型号：详解Mysql case then使用 |
| 主频：Ghz |
| 睿频：Ghz |
| 核心数：个 |
| 不支持超核心 |
| 制作工艺： |
| 插槽类型： |
| 功耗：0W |
| L3缓存：0MB |
| 支持最大内存： 0GB |
| CPU详细参数 |
|
表的创建 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语句进行日期比较的话，需要对年加引号，要不然可能结果和预期的结果不同， 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 |
| 下一个产品 SQL计算timestamp的差值的方法 上一个产品 MySQL Proxy的安装及基本命令使用教程 |