mysql数据库查询语句|mysql数据库case语句的使用方法

更新时间:2020-03-12    来源:mysql教程    手机版     字体:

【www.bbyears.com--mysql教程】

表A:如图

mysql数据库case语句的使用方法

表B:如图

 

mysql数据库case语句的使用方法

 

想输出master_id下的status=6的order数量,想要输出的结果如下图:

 

c

一开始所采取的语句是:select count(a.order_id) from a left join b on a.order_id=b.order_id where b.status=6 group by a.master_id ,发现,当status=6的这条数据不存在是查询的结果就是空,并不是像想象中返回0。

用case语句就可以很好的解决这个问题:

 代码如下

select a.master_id,sum(case when b.status=6 then 1 else 0 end) as total_count from a inner join b on a.order_id=b.id group by a.master_id.

用case语句执行时,当满足status=6时就返回1否则返回0,用sum计算出符合条件的数量。

好了现在再整理一些简单的例子供各位参考。

 代码如下

表的创建
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语句进行日期比较的话,需要对年加引号,要不然可能结果和预期的结果不同,
当然也可以用year函数来实现
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"

本文来源:http://www.bbyears.com/shujuku/86580.html

热门标签

更多>>

本类排行