Sql中的case的用法
Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
#无法得到‘第二类’这个结果
CASE
WHEN name IN( '小明', '小红') THEN '第一类'
WHEN name IN ('小红') THEN '第二类'
ELSE '其他' END
练习题目
题目1:按月度显示销售额
测试表
#创建月度销售表
CREATE TABLE sales (
year int(10), #年份
month int(10), #月份
amount int(10) #销售总额
);
测试数据
INSERT INTO sales VALUES (1991,1,11);
INSERT INTO sales VALUES (1991,2,12);
INSERT INTO sales VALUES (1991,3,13);
INSERT INTO sales VALUES (1991,4,14);
INSERT INTO sales VALUES (1992,1,21);
INSERT INTO sales VALUES (1992,2,22);
INSERT INTO sales VALUES (1992,3,23);
INSERT INTO sales VALUES (1992,4,24);
如图所示:
题目:若想查询出下面的结果(年份和月份分组),应该如何写sql语句?
思路:case when + group by
输入以下查询语句:
SELECT YEAR,
sum( CASE WHEN MONTH = 1 THEN amount ELSE 0 END ) AS m1, #sum和case结合,实现分段统计,如果month=1,
#sum+=amount的值,否则sum+0;得出的结果以m1为列名。
sum( CASE WHEN MONTH = 2 THEN amount ELSE 0 END ) AS m2,
sum( CASE WHEN MONTH = 3 THEN amount ELSE 0 END ) AS m3,
sum( CASE WHEN MONTH = 4 THEN amount ELSE 0 END ) AS m4
FROM
sales
group by
year
题目二:为商品分类,并统计总数
测试表:
create table goods ( #创建货物表
name varchar(10),
amount int (10)
)
测试数据:
INSERT INTO goods VALUES ( '苹果', 100 );
INSERT INTO goods VALUES ( '荔枝', 60 );
INSERT INTO goods VALUES( '铅笔', 220 );
INSERT INTO goods VALUES( '本子', 55 );
INSERT INTO goods VALUES( '大白菜', 66 );
INSERT INTO goods VALUES( '包菜', 23 );
INSERT INTO goods VALUES( '皮鞋', 46);
INSERT INTO goods VALUES( '衬衫', 22 );
INSERT INTO goods VALUES( '冬瓜', 25 );
select sum(amount),
case name
when '苹果' then '水果'
when '荔枝' then '水果'
when '铅笔' then '文具'
when '本子' then '文具'
when '大白菜' then '蔬菜'
when '包菜' then '蔬菜'
when '冬瓜' then '蔬菜'
when '皮鞋' then '服饰'
when '衬衫' then '服饰'
else '其它' end
from goods
这条查询语句select了两个字段:sum(amount)和 case name 。name里面本来有很多值,“苹果”、“荔枝”、“铅笔”、“本子”等等,case when then else end 语句就像相当于按照when的条件,将name分成了4类:“水果”、“文具”、“服饰”、“其他”;如果直接用case else then 进行类的话,查询结果只会显示一条(只对货品类型进行分类)所以还需要对查询结果再利用条件进行一次分组。
SELECT
(
CASE
NAME
WHEN '苹果' THEN'水果'
WHEN '荔枝' THEN '水果'
WHEN '铅笔' THEN '文具'
WHEN '本子' THEN '文具'
WHEN '大白菜' THEN'蔬菜'
WHEN '包菜' THEN'蔬菜'
WHEN '冬瓜' THEN'蔬菜'
WHEN '皮鞋' THEN'服饰'
WHEN '衬衫' THEN'服饰'
ELSE '其它'
END
) AS '分类',
sum( amount )
FROM goods
GROUP BY
CASE
NAME
WHEN '苹果' THEN'水果'
WHEN '荔枝' THEN '水果'
WHEN '铅笔' THEN '文具'
WHEN '本子' THEN '文具'
WHEN '大白菜' THEN'蔬菜'
WHEN '包菜' THEN'蔬菜'
WHEN '冬瓜' THEN'蔬菜'
WHEN '皮鞋' THEN'服饰'
WHEN '衬衫' THEN'服饰'
'服饰' ELSE '其它' END
结果如下:(为了结果好看,这里调换了一下sum(amount)的查询顺序