mysql select case 大于_mySql case when使用

使用case when语句

1。

select name, case when birthday'1988' then 'yong' else 'ok' END YORN from lee;

2。

select NAME, case name when 'sam' then 'yong' when 'lee' then 'handsome' else 'good' end from lee;

当然了case when语句还可以复合

3。

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

在这里用sql语句进行日期比较的话，需要对年加引号。要不然可能结果可能和预期的结果会不同。我的mysql版本5.1

当然也可以用year函数来实现，以第一个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);

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

2.#统计出属于low的罚款编号。

第一道题的解法与上面的相同 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`

2.#统计出属于low的罚款编号。重点看这里的解决方法 方法1. select paymentno,amount from `penalties` where 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 ='low';

方法2 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';