--测试数据准备
-- 建表
create table student(
stu_id varchar2(4),
stu_name varchar2(100), --姓名
sex varchar2(1), --性别 1 男 2 女 0 未知
credit integer default 0
);
-- 插入数据
insert into student (stu_id, stu_name, sex, credit) values ('0001', '大王', '2', '83');
insert into student (stu_id, stu_name, sex, credit) values ('0002', '刘一', '1', '85');
insert into student (stu_id, stu_name, sex, credit) values ('0003', '陈二', '2', '86');
insert into student (stu_id, stu_name, sex, credit) values ('0004', '张三', '0', '77');
insert into student (stu_id, stu_name, sex, credit) values ('0005', '李四', '1', '74');
insert into student (stu_id, stu_name, sex, credit) values ('0006', '王五', '0', '73');
insert into student (stu_id, stu_name, sex, credit) values ('0007', '赵六', '1', '65');
insert into student (stu_id, stu_name, sex, credit) values ('0008', '孙七', '2', '69');
insert into student (stu_id, stu_name, sex, credit) values ('0009', '周八', '2', '79');
insert into student (stu_id, stu_name, sex, credit) values ('0010', '吴九', '1', '55');
insert into student (stu_id, stu_name, sex, credit) values ('0011', '郑十', '1', '76');
--为了测试数据,没有控制主键唯一性
insert into student (stu_id, stu_name, sex, credit) values ('0001', '大王', '2', '83');
insert into student (stu_id, stu_name, sex, credit) values ('0002', '刘一', '1', '85');
insert into student (stu_id, stu_name, sex, credit) values ('0002', '刘一', '', '85');
commit;
--case when 使用表达式确定返回值
--一、casewhen语法---------------------------------
--1.简单Case函数
select case sex
when '1' then '男'
when '2' then '女'
else '其他'
end as sex
from student;
--2.Case搜索函数
select case
when sex = '1' then '男'
when sex = '2' then '女'
else '其他'
end as sex
from student;
--3.表达式函数和搜索函数比较-----------------------------
--搜索函数可以识别到空值
select case
when sex is null then '男'
when sex = '2' then '女'
else '其他'
end as sex
from student where stu_name = '刘一';
--表达式函数无法识别到空值
select case sex
when null then '男'
when '2' then '女'
else '其他'
end as sex
from student where stu_name = '刘一';
--3.表达式函数和搜索函数比较-----------------------------
--一、casewhen语法---------------------------------
--二、Case函数执行顺序,只返回第一个符合条件的值,剩下的Case部分将会被自动忽略
--①有排序关系的,可以按照升序关系排序
select stu_id, credit,
case
when credit < 80 then '良好'
when credit < 100 then '优秀'
end
from student;
--② 使用降序排序永远也不可能得到想要的结果,也可以说这种写法是错误的
select stu_id,credit,
case
when credit < 100 then '优秀'
when credit < 80 then '良好'
end
from student;
--③ ①写法等价于③,②是一个错误的例子
select stu_id,credit,
case
when credit < 100 and credit >= 80 then '优秀'
when credit < 80 then '良好'
end
from student;
--二、Case函数执行顺序,只返回第一个符合条件的值,剩下的Case部分将会被自动忽略
--三、case函数功能测试--------------------------------------------------------
--1.放在select中统计某一大类下面的另一个分类下的数据---------------
select credit,
count(case when sex = 1 then 1 else null end) 男生数,
count(case when sex = 2 THEN 1 else null end) 女生数
from student
group by credit;
--1.放在select中统计某一大类下面的另一个分类下的数据---------------
--2.放在where中对某一类信息作出限制------------
select *
from student
where (case
when sex = 1 then '性别明确'
when sex = 2 THEN '性别明确'
else '性别不明确'
end) = '性别不明确';
--2.放在where中对某一类信息作出限制------------
--3.放在group by后面对某一类信息进行分类-------------------------
select case
when credit < 80 then '良好'
when credit < 100 then '优秀'
end distinction,
count(*)
from student
group by case
when credit < 80 then '良好'
when credit < 100 then '优秀'
end;
--3.放在group by后面对某一类信息进行分类-------------------------
--三、case函数功能测试--------------------------------------------------------