mysql之case when then 经典用法

题目要求：

表stu

create table stu (
    id int(10) not null unique auto_increment PRIMARY KEY,
    name VARCHAR(255) not null unique
) ENGINE = innodb charset = utf8mb4;

insert into stu (name) values("zhangsan"), ("list"), ("wanger"), ("mazi"), ("wu"), ("liu"), ("qi"), ("ba"), ("jiu"), 
("ten"), ("eleven"), ("twelve"), ("thirteen"), ("fourteen");

alter table stu add column sex varchar(2) not null default 'm';

update stu set sex = 'wm' where id in(2, 4, 6, 8, 10, 12);

表score

create table score(
    id int(10) not null unique auto_increment PRIMARY key,
    sid int(10) not null,
    type int(10) not null,
    score int(10) not null
) ENGINE = INNODB charset = utf8mb4;

insert into score(sid, type, score) VALUES(1, 1, 60), (1, 2, 70), (2, 1, 50), (2, 2, 80), (3, 1, 31), (3, 2, 68), (4, 1, 10), (4, 2, 90), (5, 1, 35), (5, 2, 60), (6, 1, 56), (6, 2, 85), (7, 1, 52), (7, 2, 78), (8, 1, 87), (8, 2, 56), (9, 1, 45), (9, 2, 43), (10, 1, 54), (10, 2, 33), (11, 1, 67), (11, 2, 53), (12, 1, 85), (12, 2, 78), (13, 1, 84), (13, 2, 93), (14, 1, 74), (14, 2, 48); 

表course

create table course(
    id int(10) not null unique auto_increment PRIMARY key,
    name varchar(255) not null
)ENGINE = INNODB charset = utf8mb4;

insert into course (name) values("china"), ("english");

需求：英语（english）排名前十的人中男女比例

分析

• 首先查询排名前十的人的性别

select stu.sex from stu 
left join score s on stu.id = s.sid 
left join course c on s.type = c.id 
where c.name = 'english' 
order by s.score desc 
limit 0, 10

• case when then 分组累加

select count(*) as tc, 
sum(case sex when 'm' then 1 else 0 end)/count(*) as 'mp', 
sum(case sex when 'wm' then 1 else 0 end)/count(*) as 'wp' 
from 
(select stu.sex from stu 
left join score s on stu.id = s.sid 
left join course c on s.type = c.id 
where c.name = 'english' 
order by s.score desc 
limit 0, 10) t;

需求2：最偏科的十名学生，（中文-英语）分差最大

分析

• 首先拿到需要的信息，course.name, score.score, score.sid

select sc.score, c.name, sc.sid from score sc left join course c on sc.type = c.id

• case when then + group by 分组

select s.sid, max(case s.name when 'english' then score else 0 end) 'e', 
max(case s.name when 'china' then score else 0 end) 'c' from 
(select sc.score, c.name, sc.sid from score sc 
left join course c on sc.type = c.id) s group by s.sid

• order by + limit + left join

select stu.id, abs(e - c) as dif, stu.name, t.e, t.c from (
select s.sid, max(case s.name when 'english' then score else 0 end) 'e', max(case s.name when 'china' then score else 0 end) 'c' from 
(select sc.score, c.name, sc.sid from score sc left join course c on sc.type = c.id) s group by s.sid) t
 left join stu on t.sid = stu.id order by dif desc limit 0, 10;

另一种写法：

select s.sid, abs(max(case s.name when 'english' then score else 0 end) - max(case s.name when 'china' then score else 0 end)) as dif from 
(select sc.score, c.name, sc.sid from score sc 
left join course c on sc.type = c.id) s 
left join stu on s.sid = stu.id
group by stu.id
order by dif desc
limit 0, 10;