牛客网sql练习笔记（一）

这些是我从牛客网题目中选出来我认为具有一定难度，我需要再次练习的题目

第2题

描述

有一个员工employees表简况如下:

查找employees里入职员工时间排名倒数第三的员工所有信息

解答

由于没有说明如果相同入职时间时排序应该如何确定，所以我认为保险起见应该根据具体需要选用对应的窗口函数，我这里默认是采用连续不间断的排序方式

三个表嵌套

1. 添加一列显示入职时间的排序（从大到小）
2. 找出第三大入职时间的员工id
3. 查出员工的全部信息

SELECT * from employees e
where e.emp_no = 
(SELECT es.emp_no from 
                (select *,ROW_NUMBER() over(order by hire_date desc) as row_num
                 from employees) es
                  where es.row_num = 3)

第12题

描述

有一个员工表dept_emp如下:

有一个薪水表salaries简况如下:

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列，以上例子输出如下:

解答

添加一列排序，然后根据排序找到薪水最高的员工

SELECT t.dept_no,t.emp_no, t.maxSalaey from (
    SELECT dept_no,emp_no,salary as maxSalaey,
    ROW_NUMBER() over(partition by dept_no order by salary desc) as rn
    from dept_emp e
    join salaries s
    USING(emp_no) ) t
where rn= 1

第15题

描述

有一个员工表employees简况如下:

请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select *  from employees
where emp_no regexp '[1|3|5|7|9]$'
 	and last_name != "Mary"
 order by hire_date desc

第18题

描述

有一个员工表employees如下:

有一个薪水表salaries如下:

查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成

解答

由于不能使用order by所以不能像之前一样用添加一列排序的方法，我的想法是：

1. 先找到最高薪水
2. 取出除去最高薪水后，剩余的最高薪水即为第二高薪水
3. 找到第二高薪水的员工具体信息

select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s 
join employees e
on s.emp_no = e.emp_no
where s.salary =              -- 第三步: 将第二高工资作为查询条件
   	( select max(salary)        -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
  	  from salaries
      where salary <   
      			 (select max(salary)    -- 第一步: 查出原表最高工资
      			   from salaries	
      			   where to_date = '9999-01-01' )
  			 and to_date = '9999-01-01'
   )
   and s.to_date = '9999-01-01'

第21题

描述

员工表employees如下:
薪水表salaries如下:

查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01’时，表示依然在职，无后续调整记录）

解答

由于需要查找的是在职员工工资涨幅，所以只用到了to_date='9999-01-01’的工资和员工入职时的工资

select x.emp_no, x.salary - y.salary as growth
from(select emp_no, salary
     from salaries 
     where to_date ="9999-01-01")x
join(select emp_no, salary
     from salaries ss
     join employees
     using(emp_no)
      where hire_date = from_date)y
using(emp_no)
order by growth

第25题

描述

部门关系表dept_emp如下:

部门经理表dept_manager如下:

薪水表salaries如下:

获取员工其当前的薪水比其manager当前薪水还高的相关信息，
第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

解答

1. 分别给dept_emp，dept_manager加上薪水列
2. 把两个表联起来，找出比经理工资高的员工

select es.emp_no, ms.emp_no as manager_no,
    es.salary as emp_salary ,ms.salary as manager_salary
from(select de.emp_no, s.salary, de.dept_no
     from dept_emp de
     join salaries s
     using(emp_no))es
join(select dm.emp_no, s.salary,dm.dept_no
     from dept_manager dm
     join salaries s
     using(emp_no))ms
using(dept_no)
where es.salary > ms.salary