表的創(chuàng)建
|
1
2
3
4
5
|
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 |
數(shù)據(jù)插入：
|
1
2
3
|
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'); |
第一種用法:
|
1
2
3
4
5
|
SELECT name, CASE WHEN birthday < '1981' THEN 'old'WHEN birthday > '1988' THEN 'yong' ELSE 'ok' END YORNFROM lee |
第二種用法：
|
1
2
3
4
5
|
SELECT NAME, CASE name WHEN 'sam' THEN 'yong' WHEN 'lee' THEN 'handsome' ELSE 'good' END as oldnameFROM lee |
第三種：當(dāng)然了，case when 語句還可以復(fù)合
|
1
2
3
4
5
6
|
select name, birthday, casewhen birthday > '1983' then 'yong' when name='lee' then 'handsome' else 'just so so' endfrom lee; |
在這里用sql語句進行日期比較的話，需要對年加引號，要不然可能結(jié)果和預(yù)期的結(jié)果不同，
當(dāng)然也可以用year函數(shù)來實現(xiàn)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
select name, case when year(birthday) > 1988 then 'yong' when year(birthday) < 1980 then 'old' else 'ok' ENDfrom 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的罰款
|
1
2
3
4
5
6
7
|
select payment_date, amount, casewhen amount >= 0 AND amount < 40 then 'low' when amount >=40 AND amount < 80 then 'moderate' when amount >=80 then 'high'else 'null' ENDFROM penalties |
第二題:統(tǒng)計出屬于low的罰款編號
|
1
2
3
4
5
6
7
8
9
|
select * from( select paymentno, amount, casewhen 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 pwhere p.lvl = 'low' |
PS：Mysql，Case When,Case多個字段
|
1
2
3
4
5
6
7
8
9
|
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 |
