mysql 语句case when

4-28 2,764 views

表的创建

 

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’;

mysql开启表锁备份数据

1、全局读锁定: FLUSH TABLES WITH READ LOCK ; 执行了命令之后所有库所有表都被锁定只读,一般用在数据库联机备份,这个时候数据库的写操作将被阻塞,读操...

阅读全文

Mysql事务的四种隔离级别(转)

转载地址(click me) 数据库事务的隔离级别有4种,由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。而且,在事务的并...

阅读全文

centos yum安装mysql5.7 或 mysql8.0

参考:http://www.koukousky.com/back/1296.html 参考:https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/ $ wget https://dev.mysql.com/get/mysql...

阅读全文

欢迎留言