牛客网 SQL刷题

1.查找最晚入职员工的所有信息
解决方案： 本题重在，如何获取最晚入职时间（前提需要注意，最晚入职员工有可能不止一个人，因为在此入职时间是以天为单位的），所以这里需要嵌套子查询，先获取最晚入职时间，再将该时间当作条件来查询。

select * 
from employees
where hire_date = (select max(hire_date) from employees) 

2.查找入职员工时间排名倒数第三的员工所有信息
解决方案：
limit m,n — m代表从第几条开始，n代表需要查找几条

select * 
from employees 
where hire_date = (select hire_date 
                   from employees 
                   order by hire_date desc limit 2,1)

3. 查找当前薪水详情以及部门编号dept_no

select a.emp_no,a.salary,a.from_date,a.to_date,b.dept_no 
from salaries as a inner join dept_manager as b
on a.emp_no=b.emp_no
order by a.emp_no

4.查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示

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

5.查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工，

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

6.查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t
考查使用GROUP BY子句及聚合函数COUNT得出薪水变动次数大于15的员工

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

7.获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

select dept_emp.emp_no,dept_manager.emp_no as manager
 from dept_emp,dept_manager
where dept_emp.dept_no=dept_manager.dept_no 
and dept_emp.emp_no!=dept_manager.emp_no 

8.获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

SELECT de.dept_no,de.emp_no,MAX(sa.salary)
FROM dept_emp de INNER JOIN salaries sa
ON de.emp_no=sa.emp_no
GROUP BY de.dept_no
order by de.dept_no, de.emp_no desc;

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

select * from employees
where emp_no%2=1 and last_name!="Mary"
order by hire_date desc

10.请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序

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

11.请你获取薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no,salary from salaries
order by salary desc 
limit 1,1

12.请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，
★★★解题思路：
order by 函数的主要作用是可以进行一个排序，现在不使用找个函数，要找到第二大的数的话，思路是先找到最大的数，在把找个数从结果集中去掉，再次寻找最大的数，得出来的就是第二大的数了。

select s.emp_no,max(s.salary) as salary,last_name,first_name 
from salaries s inner join employees e
on s.emp_no=e.emp_no
where s.salary not in (select max(salary) from salaries)

13.请你查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工，

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

14.请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
思路：先建立两张表，sNow存放当前工资，sStart存放刚开始的时候的工资，使用inner join来连接两个表，然后再使用当前工资-开始时候的工资

SELECT sNow.emp_no,(sNow.salary - sStart.salary) AS growth
FROM (SELECT s.emp_no, salary
     FROM employees e
     INNER JOIN salaries s
      ON e.emp_no = s.emp_no
      WHERE s.to_date = '9999-01-01') AS sNow
      INNER JOIN(SELECT s.emp_no,salary
                FROM employees e
                INNER JOIN salaries s 
                ON e.emp_no = s.emp_no
                WHERE s.from_date = e.hire_date) AS sStart
      ON sNow.emp_no = sStart.emp_no
ORDER BY growth;

15.请你统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序

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

16.对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列且按照emp_no升序排列：
思路：1、count的是distinct
2、重点是 s1.salary <= s2.salary group by s1.emp_no
这样count就是rank排名，是在输出s1.salary的情况下，有多 少个s2.salary大于等于s1.salary

select s1.emp_no,s1.salary,count(distinct s2.salary) as rank
from salaries s1,salaries s2
where s1.salary <= s2.salary 
group by s1.emp_no 
order by s1.salary desc,s1.emp_no