牛客题霸-SQL篇（刷题记录一）

本文基于前段时间学习总结的 MySQL 相关的查询语法，在牛客网找了相应的 MySQL 题目进行练习，以便加强对于 MySQL 查询语法的理解和应用。

由于涉及到的数据库表较多，因此本文不再展示，只提供 MySQL 代码与示例输出。

以下内容是牛客题霸-SQL 篇的第 195 - 220 道题目的 MySQL 代码答案。

SQL 195：查找最晚入职员工的所有信息

# 方法一
select * from employees
where hire_date = (
    select max(hire_date) from employees
)


# 方法二：窗口函数
with a as(
	select *,
	rank() over(order by hire_date desc) as rn
	from employees
)

select emp_no, birth_date, first_name, 
last_name, gender, hire_date
from a
where rn = 1

SQL 196：查询入职员工时间排名倒数第三的员工所有信息

# 方法一
select * from employees
where hire_date = (
    select distinct hire_date from employees
    order by hire_date desc
    limit 2, 1
)


# 方法二：窗口函数
with a as(
    select emp_no, birth_date, first_name, 
    last_name, gender, hire_date,
    dense_rank() over(order by hire_date desc) as rn
    from employees
)

select emp_no, birth_date, first_name, 
last_name, gender, hire_date
from a
where rn = 3

SQL 197：查找当前薪水详情以及部门编号 dept_no

select s.emp_no, s.salary, s.from_date, s.to_date, dm.dept_no
from salaries s
join dept_manager dm
on s.emp_no = dm.emp_no
order by s.emp_no

SQL 198：查询所有已经分配部门的员工的 last_name 和 first_name 及 dept_no

select e.last_name, e.first_name, de.dept_no
from employees e
join dept_emp de
on e.emp_no = de.emp_no

SQL 199：查询所有已经分配部门的员工的 last_name ，first_name 和 dept_no，也包括暂时没有分配具体部门的员工

select last_name, first_name, dept_no
from employees e
left join dept_emp de
on e.emp_no = de.emp_no

SQL 201：查询薪水记录超过 15 条的员工号 emp_no 及其对应的记录次数 t

select emp_no, count(*) as t
from salaries
group by emp_no
having t > 15

SQL 202：查询所有员工具体的 salary 情况，对于相同的 salary 只显示一次,并按照降序排列

select distinct salary
from salaries
order by salary desc

SQL 204：查询所有非部门领导的员工的 emp_no

select e.emp_no
from employees e
left join dept_manager dm
on e.emp_no = dm.emp_no
where dm.emp_no is null

SQL 205：查询所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

select de.emp_no as emp_no, dm.emp_no as manager
from dept_emp de
join dept_manager dm
on de.dept_no = dm.dept_no
where de.emp_no <> dm.emp_no

SQL 206：查询每个部门中当前员工薪水最高的相关信息，给出 dept_no, emp_no 及其对应的 salary，按照部门编号 dept_no 升序排列

with a as(
	select de.dept_no, de.emp_no, salary,
	rank() over(partition by de.dept_no order by salary desc) as rn
	from dept_emp de
	join salaries s
	on de.emp_no = s.emp_no
)

select dept_no, emp_no, salary as maxSalary
from a
where rn = 1
order by dept_no

SQL 209：查询所有 emp_no 为奇数，且 last_name 不为 Mary 的员工信息，并按照 hire_date 降序排列

select *
from employees
where emp_no % 2 <> 0 and last_name <> 'Mary'
order by hire_date desc

SQL 210：查询每个 title 类型对应的平均工资，并按平均工资升序排序

select title, avg(salary)
from titles t
join salaries s
on t.emp_no = s.emp_no
group by title
order by avg(salary)

SQL 211：查询薪水第二多的员工的 emp_no 以及其对应的薪水 salary

# 方法一
select emp_no, salary from salaries
where salary = (
    select distinct salary  from salaries
    order by salary desc
    limit 1,1
)


# 方法二：窗口函数
with a as(
    select *,
    rank() over(order by salary desc) as rn
    from salaries
)

select emp_no, salary
from a
where rn = 2

SQL 212：查询薪水排名第二多的员工编号 emp_no，薪水 salary，last_name 以及 first_name，不能使用 order by

select e.emp_no, salary, last_name, first_name
from employees e
join salaries s
on e.emp_no = s.emp_no
where salary = (
	select max(salary) from salaries
	where salary < (
		select max(salary) from salaries
	) 
)

SQL 213：查询所有员工的 last_name，first_name 以及对应的 dept_name，也包括暂时没有分配部门的员工

select last_name, first_name, dept_name
from employees e
left join dept_emp de
on e.emp_no = de.emp_no
left join departments d
on de.dept_no = d.dept_no

SQL 215：查询在职员工自入职以来的薪水涨幅情况，给出在职员工编号 emp_no 以及其对应的薪水涨幅 growth，并按照 growth 升序排列

with a as(
	select emp_no
    from salaries
    where to_date = '9999-01-01'
)

select emp_no,
sum(gap) as growth
from(
	select s1.emp_no,
	s2.salary - s1.salary as gap
	from salaries s1
	left join salaries s2
	on s1.to_date = s2.from_date
) b
where emp_no in(
	select emp_no from a
)
group by emp_no
order by growth

SQL 216：查询各个部门的工资记录数，部门号 dept_no，部门名称 dept_name 以及部门在 salaries 表里面有多少条记录，按照 dept_no 升序排列

select d.dept_no, d.dept_name, count(salary) as sum
from departments d
join dept_emp de
on d.dept_no = de.dept_no
join salaries s
on de.emp_no = s.emp_no
group by d.dept_no
order by d.dept_no

SQL 217：对所有员工按照 salary 降序先进行 1 - N 的排名，如果 salary 相同，再按照 emp_no 升序排列

select emp_no, salary,
dense_rank() over(order by salary desc)
from salaries

SQL 218：查询所有非 manager 员工的 dept_no，emp_no 以及salary

select de.dept_no,de.emp_no,salary
from dept_emp de 
join salaries s
on de.emp_no = s.emp_no
where de.emp_no not in(
    select emp_no from dept_manager
)

SQL 219：查询员工当前的薪水比其 manager 当前薪水还高的相关信息

with ma_sa as(
	select dm.dept_no, 
    dm.emp_no as manager_no, 
    s1.salary as manager_salary
	from dept_manager dm
	join salaries s1
	on dm.emp_no = s1.emp_no
)

select de.emp_no, manager_no, 
s2.salary as emp_salary, manager_salary
from dept_emp de
join salaries s2
on de.emp_no  = s2.emp_no
join ma_sa
on de.dept_no = ma_sa.dept_no
where de.emp_no <> manager_no and s2.salary > manager_salary

SQL 220：查询各个部门当前员工的 title 类型的分配数目，即结果给出部门编号dept_no，部门名称 dept_name，其部门下所有的员工的 title 以及该 title 对应的数目 count，结果按照 dept_no 升序排序，dept_no 一样的再按 title 升序排序

select d.dept_no, d.dept_name, title, count(*) as count
from departments d
join dept_emp de
on d.dept_no = de.dept_no
join titles t
on de.emp_no = t.emp_no
group by 1, 2, 3
order by 1, 3