mysql case when语句

表的创建

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\');

使用case when语句

1。

select name,
case 
when birthday<\'1981\' then \'old\'
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\';