力扣每日SQL刷题(574,577,578,579,580)

力扣_574

说明

点击跳转详情

分析

实现

准备工作

Create table If Not Exists Candidate
(
    id   int,
    name varchar(255)
);
Create table If Not Exists Vote
(
    id          int,
    candidateId int
);
Truncate table Candidate;
insert into Candidate (id, name)
values ('1', 'A');
insert into Candidate (id, name)
values ('2', 'B');
insert into Candidate (id, name)
values ('3', 'C');
insert into Candidate (id, name)
values ('4', 'D');
insert into Candidate (id, name)
values ('5', 'E');
Truncate table Vote;
insert into Vote (id, candidateId)
values ('1', '2');
insert into Vote (id, candidateId)
values ('2', '4');
insert into Vote (id, candidateId)
values ('3', '3');
insert into Vote (id, candidateId)
values ('4', '2');
insert into Vote (id, candidateId)
values ('5', '5');

问题

– 编写解决方案来报告获胜候选人的名字(即获得最多选票的候选人)。
– 生成的测试用例保证 只有一个候选人赢得 选举

实现方法

with t1 as (select candidateId, count(id) num
            from vote
            group by candidateId
            order by num desc
            limit 1)
select name
from candidate
where id = (select candidateId from t1);

小结

聚合函数
子查询

力扣_577题

说明

点击跳转详情

分析

1.由于这个问题的查询结果要求没有数据的显示为null,所以要用外连接(left/right join on)
2.然后筛选奖金大于1000的,和没有奖金的员工即可.

实现

准备工作

Create table If Not Exists Employee
(
    empId      int,
    name       varchar(255),
    supervisor int,
    salary     int
);
Create table If Not Exists Bonus
(
    empId int,
    bonus int
);
Truncate table Employee;
insert into Employee (empId, name, supervisor, salary)
values ('3', 'Brad', null, '4000');
insert into Employee (empId, name, supervisor, salary)
values ('1', 'John', '3', '1000');
insert into Employee (empId, name, supervisor, salary)
values ('2', 'Dan', '3', '2000');
insert into Employee (empId, name, supervisor, salary)
values ('4', 'Thomas', '3', '4000');
Truncate table Bonus;
insert into Bonus (empId, bonus)
values ('2', '500');
insert into Bonus (empId, bonus)
values ('4', '2000');

问题

– 编写解决方案，报告每个奖金 少于 1000 的员工的姓名和奖金数额

实现方法

select name, bonus
from employee
         left join Bonus
                   on employee.empId = Bonus.empId
where bonus < 1000
   or bonus is null;

小结

力扣_578题

说明

点击跳转详情

分析

根据问题id分组,统计不同状态下的次数.

实现

准备工作

Create table If Not Exists SurveyLog
(
    id          int,
    action      varchar(255),
    question_id int,
    answer_id   int,
    q_num       int,
    timestamp   int
);
Truncate table SurveyLog;
insert into SurveyLog (id, action, question_id, answer_id, q_num, timestamp)
values ('5', 'show', '285', null, '1', '123');
insert into SurveyLog (id, action, question_id, answer_id, q_num, timestamp)
values ('5', 'answer', '285', '124124', '1', '124');
insert into SurveyLog (id, action, question_id, answer_id, q_num, timestamp)
values ('5', 'show', '369', null, '2', '125');
insert into SurveyLog (id, action, question_id, answer_id, q_num, timestamp)
values ('5', 'skip', '369', null, '2', '126');

问题

– 回答率 是指：同一问题编号中回答次数占显示次数的比率。
– 编写一个解决方案以报告 回答率 最高的问题。
– 如果有多个问题具有相同的最大 回答率 ，返回 question_id 最小的那个

实现方法

select question_id as survey_log
from SurveyLog
group by question_id
order by sum(action = 'answer') / sum(action = 'show') desc, question_id
limit 1;

小结

普普通通的分组,注意细节.

力扣_579题

说明

点击跳转详情

分析

实现

准备工作

Create table If Not Exists Employee
(
    id     int,
    month  int,
    salary int
);
Truncate table Employee;
insert into Employee (id, month, salary)
values ('1', '1', '20');
insert into Employee (id, month, salary)
values ('2', '1', '20');
insert into Employee (id, month, salary)
values ('1', '2', '30');
insert into Employee (id, month, salary)
values ('2', '2', '30');
insert into Employee (id, month, salary)
values ('3', '2', '40');
insert into Employee (id, month, salary)
values ('1', '3', '40');
insert into Employee (id, month, salary)
values ('3', '3', '60');
insert into Employee (id, month, salary)
values ('1', '4', '60');
insert into Employee (id, month, salary)
values ('3', '4', '70');
insert into Employee (id, month, salary)
values ('1', '7', '90');
insert into Employee (id, month, salary)
values ('1', '8', '90');

问题

/*
编写一个解决方案，在一个统一的表中计算出每个员工的 累计工资汇总 。
员工的 累计工资汇总 可以计算如下:
对于该员工工作的每个月，将 该月 和 前两个月 的工资 加 起来。这是他们当月的 3 个月总工资和 。如果员工在前几个月没有为公司工作，那么他们在前几个月的有效工资为 0 。
不要 在摘要中包括员工 最近一个月 的 3 个月总工资和。
不要 包括雇员 没有工作 的任何一个月的 3 个月总工资和。
返回按 id 升序排序 的结果表。如果 id 相等，请按 month 降序排序
*/

实现方法

with t1 as (select id,
                   month,
                   salary,
                   lag(month) over (partition by id order by id,month)             front_month,
                   ifnull(lag(salary) over (partition by id order by id,month), 0) front_salary
            from employee)
   , t2 as (select id,
                   month,
                   if(month - front_month = 1, salary + front_salary, salary) as      Salary,
                   lag(month, 2) over (partition by id order by id,month)             front_month_2,
                   ifnull(lag(salary, 2) over (partition by id order by id,month), 0) front_salary_2
            from t1)
   , t3 as (select id,
                   month,
                   if(month - front_month_2 = 2, Salary + front_salary_2, Salary) as Salary,
                   rank() over (partition by id order by id,month desc)              rk
            from t2)
select id, month, Salary
from t3
where rk != 1;

小结

窗口函数
子查询

力扣_580题

说明

点击跳转详情

分析

1.题目要求返回所有的部门，即使那个部门下没有任何学生。所以在表的连接方式上，需要选用外连接。
2.题目要求统计每个部门下的学生数量。那么，可以使用GROUP BY+COUNT来统计。而又不能使用COUNT(*)或COUNT(1)，因为这样会把没有任何学生的部门下的学生数统计为1。
这里可以使用COUNT(student_id)，这样当关联不到student时，student_id为NULL值。而在计算COUNT时，NULL值是不计算在内的。
3.结果要求以学生数倒序排序返回。

实现

准备工作

Create table If Not Exists Student
(
    student_id   int,
    student_name varchar(45),
    gender       varchar(6),
    dept_id      int
);
Create table If Not Exists Department
(
    dept_id   int,
    dept_name varchar(255)
);
Truncate table Student;
insert into Student (student_id, student_name, gender, dept_id)
values ('1', 'Jack', 'M', '1');
insert into Student (student_id, student_name, gender, dept_id)
values ('2', 'Jane', 'F', '1');
insert into Student (student_id, student_name, gender, dept_id)
values ('3', 'Mark', 'M', '2');
Truncate table Department;
insert into Department (dept_id, dept_name)
values ('1', 'Engineering');
insert into Department (dept_id, dept_name)
values ('2', 'Science');
insert into Department (dept_id, dept_name)
values ('3', 'Law');

问题

– 编写解决方案，为 Department 表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。
– 按 student_number 降序 返回结果表。如果是平局，则按 dept_name 的 字母顺序 排序

实现方法

with t1 as (select dept_id, ifnull(count(student_id), 0) student_number
            from student
            group by dept_id)
select dept_name, ifnull(student_number, 0) as student_number
from department
         left join t1 on department.dept_id = t1.dept_id
order by student_number desc, dept_name;

小结

子查询
左连接