Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
Case when 相当于一个自定义的数据透视表，group by 是行名，case when 负责列名。
根据字段的值来统计不同情况的数量
CREATE TABLE `tb_base_information_property` (
`id` varchar(60) NOT NULL,
`name` varchar(60) DEFAULT NULL COMMENT '属性名称',
`code` varchar(60) DEFAULT NULL COMMENT '属性代码',
`used` varchar(1) DEFAULT NULL COMMENT '是否可用,1可0否',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='信息属性表';
SELECT
sum(case when b.used = '1' then 1 else 0 end) read_num ,
sum(case when b.used != '1' then 1 else 0 end) un_read_num
FROM tb_base_information_property b
详细介绍
case具有两种格式。简单case函数和case搜索函数。
--简单case函数
case sex
when '1' then '男'
when '2' then '女’
else '其他' end
--case搜索函数
case when sex = '1' then '男'
when sex = '2' then '女'
else '其他' end
这两种方式，可以实现相同的功能。简单case函数的写法相对比较简洁，但是和case搜索函数相比，功能方面会有些限制，比如写判定式。
还有一个需要注重的问题，case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。
--比如说，下面这段sql，你永远无法得到“第二类”这个结果
case when col_1 in ('a','b') then '第一类'
when col_1 in ('a') then '第二类'
else '其他' end 下面实例演示：
首先创建一张users表，其中包含id,name,sex三个字段，表内容如下：
SQL> drop table users purge;
drop table users purge
ORA-00942: 表或视图不存在
SQL> create table users(id int,name varchar2(20),sex number);
Table created
SQL> insert into users(id,name) values(1,'张一');
1 row inserted
SQL> insert into users(id,name,sex) values(2,'张二',1);
1 row inserted
SQL> insert into users(id,name) values(3,'张三');
1 row inserted
SQL> insert into users(id,name) values(4,'张四');
1 row inserted
SQL> insert into users(id,name,sex) values(5,'张五',2);
1 row inserted
SQL> insert into users(id,name,sex) values(6,'张六',1);
1 row inserted
SQL> insert into users(id,name,sex) values(7,'张七',2);
1 row inserted
SQL> insert into users(id,name,sex) values(8,'张八',1);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from users;
ID NAME SEX
--------------------------------------- -------------------- ----------
1 张一
2 张二 1
3 张三
4 张四
5 张五 2
6 张六 1
7 张七 2
8 张八 1
8 rows selected
1、上表结果中的"sex"是用代码表示的，希望将代码用中文表示。可在语句中使用case语句：
SQL> select u.id,u.name,u.sex,
2 (case u.sex
3 when 1 then '男'
4 when 2 then '女'
5 else '空的'
6 end
7 )性别
8 from users u;
ID NAME SEX 性别
--------------------------------------- -------------------- ---------- ------
1 张一 空的
2 张二 1 男
3 张三 空的
4 张四 空的
5 张五 2 女
6 张六 1 男
7 张七 2 女
8 张八 1 男
8 rows selected
2、如果不希望列表中出现"sex"列，语句如下：
SQL> select u.id,u.name,
2 (case u.sex
3 when 1 then '男'
4 when 2 then '女'
5 else '空的'
6 end
7 )性别
8 from users u;
ID NAME 性别
--------------------------------------- -------------------- ------
1 张一 空的
2 张二 男
3 张三 空的
4 张四 空的
5 张五 女
6 张六 男
7 张七 女
8 张八 男
8 rows selected
3、将sum与case结合使用，可以实现分段统计。
如果现在希望将上表中各种性别的人数进行统计，sql语句如下：
SQL> select
2 sum(case u.sex when 1 then 1 else 0 end)男性,
3 sum(case u.sex when 2 then 1 else 0 end)女性,
4 sum(case when u.sex <>1 and u.sex<>2 then 1 else 0 end)性别为空
5 from users u;
男性 女性 性别为空
---------- ---------- ----------
3 2 0
--------------------------------------------------------------------------------
SQL> select
2 count(case when u.sex=1 then 1 end)男性,
3 count(case when u.sex=2 then 1 end)女,
4 count(case when u.sex <>1 and u.sex<>2 then 1 end)性别为空
5 from users u;
男性 女 性别为空
---------- ---------- ----------
3 2 0
与group by结合
- 自定义列联表统计
SELECT country,
SUM( CASE WHEN sex = '1' THEN population ELSE 0 END), --男性人口
SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) --女性人口
FROM Table_A GROUP BY country; - 增加分组条件
- 自定义分组统计
/*新建一个表demo*/
CREATE TABLE demo (
type varchar(10) NULL,
num int NULL)
/*插入数据*/
insert into demo values ('A',10);
insert into demo values ('B',10);
insert into demo values ('C',10);
insert into demo values ('D',10);
insert into demo values ('E',10);
insert into demo values ('F',10);
insert into demo values ('G',10);
/*利用CASE WHEN 和 GROUP BY 实现自定义分组合计*/
select
case
when type in('A','C','D') THEN 'ACD'
when type in('F','G') THEN 'FG'
else type end as types,sum(num)
from demo
group by
case
when type in('A','C','D') THEN 'ACD'
when type in('F','G') THEN 'FG'
else type end - 原始表
- select结果
- 去重分组统计
select
count(distinct case when photo='1' then id else null end ）photo1_count,
count(distinct case when photo='2' then id else null end ）photo2_count
from photos- 根据条件执行update
UPDATE Personnel
SET salary =
CASE
WHEN salary >= 5000 THEN salary * 0.9
WHEN salary >= 2000 AND salary < 4600 THEN salary * 1.15
ELSE salary END; UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a' THEN 'b'
WHEN p_key = 'b' THEN 'a' ELSE p_key END
WHERE p_key IN ('a', 'b');
- 检查2个表是否一致
--使用IN的时候
SELECT keyCol,
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B ) THEN 'Matched'
ELSE 'Unmatched' END Label
FROM tbl_A;
--使用EXISTS的时候
SELECT keyCol,
CASE WHEN EXISTS ( SELECT * FROM tbl_B WHERE tbl_A.keyCol = tbl_B.keyCol ) THEN 'Matched' ELSE 'Unmatched' END Label
FROM tbl_A; - 统计分类
DROP TABLE IF EXISTS `cp`;
CREATE TABLE `cp` (
`country` varchar(48) NOT NULL,
`population` int(11) DEFAULT NULL,
PRIMARY KEY (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of cp
-- ----------------------------
INSERT INTO `cp` VALUES ('中国', '600');
INSERT INTO `cp` VALUES ('加拿大', '100');
INSERT INTO `cp` VALUES ('印度', '250');
INSERT INTO `cp` VALUES ('墨西哥', '50');
INSERT INTO `cp` VALUES ('德国', '200');
INSERT INTO `cp` VALUES ('日本', '250');
INSERT INTO `cp` VALUES ('法国', '300');
INSERT INTO `cp` VALUES ('美国', '100');
INSERT INTO `cp` VALUES ('英国', '200');
原数据
目标结果
sql语句
SELECT CASE country WHEN '中国' THEN '亚洲'
WHEN '印度' THEN '亚洲'
WHEN '日本' THEN '亚洲'
WHEN '美国' THEN '北美洲'
WHEN '加拿大' THEN '北美洲'
WHEN '墨西哥' THEN '北美洲'
ELSE '其他' END 洲,
SUM(population) 人口
FROM cp
GROUP BY
CASE country
WHEN '中国' THEN '亚洲'
WHEN '印度' THEN '亚洲'
WHEN '日本' THEN '亚洲'
WHEN '美国' THEN '北美洲'
WHEN '加拿大' THEN '北美洲'
WHEN '墨西哥' THEN '北美洲'
ELSE '其他' END;
同样的，我们也可以用这个方法来判断工资的等级，并统计每一等级的人数。SQL代码如下:
SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class, -- 别名命名
COUNT(*) FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;
扫一扫
4579
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
