SQL进阶--case表达式

exists 高效

-- 创建课程一览表
drop table if exists coursemaster;
create table coursemaster(
    course_id int  auto_increment,
    course_name varchar(20) not null,
    Primary key (course_id)
);
-- 插入数据
insert into coursemaster(course_id, course_name) values(1, '会计入门');
insert into coursemaster(course_name) values('财务知识');
insert into coursemaster(course_name) values('薄记考试');
insert into coursemaster(course_name) values('税务师');

-- 创建开设的课程
drop table if exists opencourses;
create table opencourses(
    month date ,
    course_id int
);
-- 插入数据
insert into opencourses(month, course_id) values(200706, 1);
insert into opencourses(month, course_id) values(200706, 3);
insert into opencourses(month, course_id) values(200706, 4);
insert into opencourses(month, course_id) values(200707, 4);
insert into opencourses(month, course_id) values(200708, 2);
insert into opencourses(month, course_id) values(200708, 4);

-- 生成交叉表
-- EXIXTS 性能高
select course_name,
       case when EXISTS 
                    (select course_id from opencourses oc 
                      where month = 200706 and oc.course_id = cm.course_id) THEN 'Ο'
       ELSE 'X' END AS '6月',
       case when EXISTS 
                    (select course_id from opencourses oc 
                      where month = 200707 and oc.course_id = cm.course_id) THEN 'Ο'
       ELSE 'X' END AS '7月',
       case when EXISTS 
                    (select course_id from opencourses oc 
                      where month = 200708 and oc.course_id = cm.course_id) THEN 'Ο'
       ELSE 'X' END AS '8月'
FROM coursemaster cm;

结果：

case表达式中使用聚合函数

-- 创建studentclub表
drop table if exists studentclub;
create table studentclub(
std_id int8,
club_id int4,
club_name varchar(20),
main_club_flg varchar(4)
);
-- 插入数据
insert into studentclub(std_id, club_id, club_name, main_club_flg) values(100,1,'棒球','Y');
insert into studentclub(std_id, club_id, club_name, main_club_flg) values(100,2,'管弦乐','N');
insert into studentclub(std_id, club_id, club_name, main_club_flg) values(200,2,'管弦乐','N');
insert into studentclub(std_id, club_id, club_name, main_club_flg) values(200,3,'羽毛球','Y');
insert into studentclub(std_id, club_id, club_name, main_club_flg) values(200,4,'足球','N');
insert into studentclub(std_id, club_id, club_name, main_club_flg) values(300,4,'足球','N');
insert into studentclub(std_id, club_id, club_name, main_club_flg) values(400,5,'游泳','N');
insert into studentclub(std_id, club_id, club_name, main_club_flg) values(500,6,'围棋','N');
-- 1、获取只加入一个社团的学生的社团ID
select std_id, max(club_id) as main_club
from studentclub
group by std_id
having count(*) = 1;
-- 2、获取加入多个 一个社团的学生的主社团ID
select std_id, club_id
from studentclub
where main_club_flg = 'Y';
-- 3、用case执行12
select std_id,
       case when count(*) = 1 -- 只加入一个人社团的学生
       then max(club_id)
       else max(case when main_club_flg = 'Y'
                     THEN club_id
                     else null end)
        end as main_club
from studentclub
group by std_id;

结果：

练习题1 多列数据的最大值

drop table if exists t11;
create table t11(
key1 varchar(5),
x int4,
y int4,
z int4
);
insert into t11(key1, x, y, z) values('A', 1, 2, 3);
insert into t11(key1, x, y, z) values('B', 5, 5, 2);
insert into t11(key1, x, y, z) values('C', 4, 7, 1);
insert into t11(key1, x, y, z) values('D', 3, 3, 8);
-- 比较三个数
select key1,
       case when max(x) > max(y) then max(X)
       else (case when max(y) >= max(z) then max(y) else max(z) end) 
       end as greatest
from t11
group by key1;

结果：

练习题2 转换行列——在表头里加入汇总和再揭

drop table if exists poptbl2;
create table poptbl2(
perf_name varchar(8),
sex int4,
population int8
);
insert into poptbl2(perf_name, sex, population) values('德岛',1,60);
insert into poptbl2(perf_name, sex, population) values('德岛',2,40);
insert into poptbl2(perf_name, sex, population) values('香川',1,100);
insert into poptbl2(perf_name, sex, population) values('香川',2,100);
insert into poptbl2(perf_name, sex, population) values('爱媛',1,100);
insert into poptbl2(perf_name, sex, population) values('爱媛',2,50);
insert into poptbl2(perf_name, sex, population) values('高知',1,100);
insert into poptbl2(perf_name, sex, population) values('高知',2,100);
insert into poptbl2(perf_name, sex, population) values('福冈',1,100);
insert into poptbl2(perf_name, sex, population) values('福冈',2,200);
insert into poptbl2(perf_name, sex, population) values('佐贺',1,20);
insert into poptbl2(perf_name, sex, population) values('佐贺',2,80);
insert into poptbl2(perf_name, sex, population) values('长崎',1,125);
insert into poptbl2(perf_name, sex, population) values('长崎',2,125);
insert into poptbl2(perf_name, sex, population) values('东京',1,250);
insert into poptbl2(perf_name, sex, population) values('东京',2,150);

select 
    case when sex = 1 then '男' else '女' end as '性别',
    case when sex = 1 then sum(population) else sum(population) end as '全国',
    sum(case when perf_name = '德岛' then  population
            else 0 end)  as '德岛',
    sum(case when perf_name = '香川' then  population
            else 0 end) as '香川',
    sum(case when perf_name = '爱媛' then  population
            else 0 end)  as '爱媛',
    sum(case when perf_name = '高知' then  population
            else 0 end)  as '高知',
    sum(case when perf_name in ('德岛', '香川', '爱媛', '高知') then population
            else 0 end) as '四国'
from poptbl2
group by sex;

结果：

内容多来自 《SQL进阶教材》，仅做笔记。习题代码均为原创。