MySQL高阶2041-面试中被录取的候选人

目录

题目

准备数据

分析数据

总结

---

题目

编写解决方案，找出 至少有两年 工作经验、且面试分数之和 严格大于 15 的候选人的 ID 。

可以以 任何顺序 返回结果表。

准备数据

Create table If Not Exists Candidates (candidate_id int, name varchar(30), years_of_exp int, interview_id int)
Create table If Not Exists Rounds (interview_id int, round_id int, score int)
    Truncate table Candidates
    insert into Candidates (candidate_id, name, years_of_exp, interview_id) values ('11', 'Atticus', '1', '101')
    insert into Candidates (candidate_id, name, years_of_exp, interview_id) values ('9', 'Ruben', '6', '104')
    insert into Candidates (candidate_id, name, years_of_exp, interview_id) values ('6', 'Aliza', '10', '109')
    insert into Candidates (candidate_id, name, years_of_exp, interview_id) values ('8', 'Alfredo', '0', '107')
    Truncate table Rounds
    insert into Rounds (interview_id, round_id, score) values ('109', '3', '4')
    insert into Rounds (interview_id, round_id, score) values ('101', '2', '8')
    insert into Rounds (interview_id, round_id, score) values ('109', '4', '1')
    insert into Rounds (interview_id, round_id, score) values ('107', '1', '3')
    insert into Rounds (interview_id, round_id, score) values ('104', '3', '6')
    insert into Rounds (interview_id, round_id, score) values ('109', '1', '4')
    insert into Rounds (interview_id, round_id, score) values ('104', '4', '7')
    insert into Rounds (interview_id, round_id, score) values ('104', '1', '2')
    insert into Rounds (interview_id, round_id, score) values ('109', '2', '1')
    insert into Rounds (interview_id, round_id, score) values ('104', '2', '7')
    insert into Rounds (interview_id, round_id, score) values ('107', '2', '3')
    insert into Rounds (interview_id, round_id, score) values ('101', '1', '8')

candidates表

rounds表

 

分析数据

with t1 as(
    select candidate_id,c.interview_id,round_id,score,
           sum(score) over(partition by c.interview_id) total
    from candidates c join rounds r on c.interview_id = r.interview_id
    where years_of_exp >= 2
)select distinct candidate_id from t1 where total > 15;

 

总结

 开窗函数sum() 和普通函数sum()的区别

开窗函数sum():也可以求其他列

select candidate_id,c.interview_id,round_id,score,
       sum(score) over(partition by c.interview_id) total
       from candidates c join rounds r on c.interview_id = r.interview_id
where years_of_exp >= 2;

普通sum():需要搭配group by使用,并且只可以求分组的列

select candidate_id,
       sum(score) total from candidates c join rounds r on c.interview_id = r.interview_id
where years_of_exp >= 2
group by candidate_id;

如果求多个列,会出现错误

select candidate_id,c.interview_id,round_id,score,
       sum(score) total from candidates c join rounds r on c.interview_id = r.interview_id
where years_of_exp >= 2
group by candidate_id;