牛客面试必考真题SQL篇72题全答案——进阶篇

本篇主要包含牛客面试必考真题SQL篇72题的进阶部分，主要包括较难级别(18题)和困难级别(4题)，题目较为复杂，我的答案可能也不是最优解哈，欢迎大家多多指正！

刷题链接：牛客精选数据库SQL实战

SQL专题文章转送门：

牛客面试必考真题SQL篇72题全答案——基础篇

MySQL进阶操作总结：窗口函数、排名函数、case用法

一文掌握python连接SQL Server，MySQL，MongoDB，Redis数据库

目录

较难级别(18道)

SQL18：查找当前薪水排名第二多的员工编号emp_no。

select employees.emp_no, salary, last_name, first_name
from (select row_number() over(order by salary desc) num, emp_no, salary
from salaries
where to_date='9999-01-01') as temp right join employees
on temp.emp_no = employees.emp_no
where num = 2

SQL21：查找所有员工自入职以来的薪水涨幅情况。

法一：(自己解法)
select employees.emp_no, max(salary)-min(salary) as growth
from (select row_number() over(partition by emp_no order by to_date) num1,
      row_number() over(partition by emp_no order by to_date desc) num2,
      emp_no, salary, to_date from salaries) as temp right join employees
on temp.emp_no = employees.emp_no
where num1 = 1 or num2 = 1
group by employees.emp_no
having max(to_date)='9999-01-01'
order by growth

法二：（参考答案，分两次查询）
select a.emp_no, b.salary - a.salary as growth
from
(select e.emp_no, s.salary
from employees e join salaries s
on e.emp_no=s.emp_no
where hire_date=from_date) as a
join
(select e.emp_no, s.salary
from employees e join salaries s
on e.emp_no=s.emp_no
where to_date='9999-01-01') as b
on a.emp_no = b.emp_no
order by growth

SQL23：对所有员工的当前薪水按照salary进行按照1-N的排名。

select emp_no, salary, dense_rank() over(order by salary desc) t_rank
from salaries
where to_date='9999-01-01'

SQL24：获取所有非manager员工当前的薪水情况。

# 没有分配部门的员工不计算在内
select dept_no, t.emp_no, salary
from (select emp_no, salary from salaries where to_date='9999-01-01' and emp_no not in (select emp_no from dept_manager where to_date='9999-01-01')) as t join dept_emp d
on t.emp_no = d.emp_no

SQL25：获取员工其当前的薪水比其manager当前薪水还高的相关信息。

# 参考思路：分别用两张表统计员工和manager的薪水
select a.emp_no, b.emp_no, a.salary, b.salary
from
(select d.emp_no, d.dept_no, s.salary
from dept_emp d join salaries s
on d.emp_no = s.emp_no
where s.to_date='9999-01-01') a
join
(select m.emp_no, m.dept_no, s.salary
from dept_manager m join salaries s
on m.emp_no = s.emp_no
where s.to_date='9999-01-01') b
on a.dept_no = b.dept_no
where a.salary>b.salary

SQL27：给出每个员工每年薪水涨幅超过5000的员工编号emp_no。

# 参考思路：自连接
select s1.emp_no, s2.from_date, s2.salary-s1.salary as salary_growth
from salaries s1 join salaries s2
on s1.to_date=s2.from_date and s1.emp_no=s2.emp_no
where s2.salary-s1.salary>5000
order by salary_growth desc

SQL28：查找描述信息中包括robot的电影对应的分类名称以及电影数目。

# 参考答案：三表全连再过滤，注意必须添加group by
select c.name, count(f.film_id)
from film f join film_category fc on f.film_id=fc.film_id
join category c on fc.category_id=c.category_id
where f.description like '%robot%' and fc.category_id in (select category_id
                                                      from film_category
                                                      group by category_id
                                                      having count(film_id)>=5)
group by c.name

SQL56：获取所有员工的emp_no。

select e.emp_no, d.dept_no, btype, received
from employees e join dept_emp d
on e.emp_no = d.emp_no
left join emp_bonus b
on e.emp_no = b.emp_no

SQL59：获取有奖金的员工相关信息。

select e.emp_no, first_name, last_name, btype, salary, (case btype
                                                        when 1 then salary*0.1 
                                                        when 2 then salary*0.2
                                                        else salary*0.3
                                                        end) bonus
from employees e join salaries s
on e.emp_no = s.emp_no
join emp_bonus b
on e.emp_no = b.emp_no
where s.to_date='9999-01-01'

注：case的用法

SQL60：统计salary的累计和running_total。

select emp_no, salary, sum(salary) over(order by emp_no) running_total
from salaries
where to_date = '9999-01-01'

注：窗口函数 + 自连接取不等条件

SQL61：对于employees表中，给出奇数行的first_name。

select e.first_name
from (select row_number() over(order by first_name asc) num, first_name from employees) as t
join employees e
on t.first_name=e.first_name
where num%2=1

SQL65：异常的邮件概率。

select e.date, round(sum(case e.type when 'no_completed' then 1 else 0 end)/count(type),3) as p
from email e 
join user u1 on e.send_id=u1.id
join user u2 on e.receive_id=u2.id
where u1.is_blacklist=0 and u2.is_blacklist=0
group by e.date
order by e.date

SQL67：牛客每个人最近的登录日期(二)

select u_n, c_n, d
from (select u.name as u_n,  c.name as c_n , l.date as d, 
row_number() over(partition by l.user_id order by l.date desc) num
from login l
join user u on l.user_id=u.id
join client c on l.client_id=c.id) as t
where num=1
order by u_n

SQL68：牛客每个人最近的登录日期(三)

select round(count(distinct user_id)/(select count(distinct user_id) from login), 3)
from login
where (user_id, date) in (select user_id, date_add(min(date), interval 1 day)
                         from login
                         group by user_id)

注：MySQL里查找某一天的后一天的用法是：date_add(yyyy-mm-dd, interval 1 day)

SQL69：牛客每个人最近的登录日期(四)

select date, sum(case num when 1 then 1 else 0 end) as new
from (select user_id, date, row_number() over(partition by user_id order by date) num
from login) as t
group by date
order by date

SQL71：牛客每个人最近的登录日期(六)

select u.name as u_n, p.date as date, sum(number) over(partition by user_id order by date) ps_num
from passing_number p join user u
on p.user_id = u.id
order by date, u_n

SQL74：考试分数(三)

select id, name, score
from (select g.id id, l.name name, score, dense_rank() over(partition by g.language_id order by score desc) num
from grade g join language l
on g.language_id=l.id) as t
where num<=2
order by name, score desc, id

SQL75：考试分数(四)

select job,
(case when count(id)%2=0 then cast(count(id)/2 as signed) else cast((count(id)+1)/2 as signed) end) as start,
(case when count(id)%2=0 then cast((count(id)+2)/2 as signed) else cast((count(id)+1)/2 as signed) end) as end
from grade
group by job
order by job

注：浮点数转为整数：cast(col as signed)

---

困难级别(4道)

SQL12：获取所有部门中当前员工薪水最高的相关信息。

select dept_no, emp_no, salary
from(select e.dept_no, e.emp_no, s.salary, rank() over(partition by e.dept_no order by s.salary desc) num
from dept_emp e join salaries s
on e.emp_no=s.emp_no
where e.to_date='9999-01-01' and s.to_date='9999-01-01') as t
where num=1
order by dept_no

SQL26： 汇总各个部门当前员工的title类型的分配数目。

select t.dept_no, d.dept_name, t.title, t.count
from(select e.dept_no, t.title, count(title) as count
from dept_emp e join titles t
on e.emp_no=t.emp_no
where e.to_date='9999-01-01' and t.to_date='9999-01-01'
group by e.dept_no, t.title) t
join departments d
on t.dept_no = d.dept_no
order by t.dept_no, t.title

SQL70：牛客每个人最近的登录日期(五)

select a.date, round(ifnull(ifnull(count_2, 0)*1.0/count_1, 0), 3) p
from
(select date, sum(case num when 1 then 1 else 0 end) as count_1
from(select user_id, date, row_number() over(partition by user_id order by date) num
from login) as t
group by date) a
left join
(select date_add(min(date), interval -1 day) as date, count(user_id) as count_2
from login
where (user_id, date) in (select user_id, date_add(min(date), interval 1 day)
                         from login
                         group by user_id)
group by date) b
on a.date = b.date

注：ifnull()用于判断第一个表达式是否为null，如果为null则返回第二个参数的值，如果不为null则返回第一个参数的值。具体形式：ifnull(expression, alt_value)

SQL76：考试分数(五)

# 注：注意partition by和group by的重复使用
select B.* from
(select job,
(case when count(id)%2=0 then cast(count(id)/2 as signed) else cast((count(id)+1)/2 as signed) end) as start,
(case when count(id)%2=0 then cast((count(id)+2)/2 as signed) else cast((count(id)+1)/2 as signed) end) as end
from grade
group by job) A
join
(select id, job, score, row_number() over(partition by job order by score desc) t_rank
from grade) B
on A.job=B.job and B.t_rank between A.start and A.end
order by B.id

本文地址：https://blog.csdn.net/xylbill97/article/details/110950942