练习SQL利器，牛客网SQL实战题库，17~24题

牛客网SQL实战网址：https://www.nowcoder.com/ta/sql
持续更新——记录自己在牛客网SQL的做题过程

17.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

题目描述：
获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

SELECT emp_no,salary
FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC
LIMIT 1,1;

注：在做题讨论时候，有的人说薪水第一的有可能哟多个，所以要去重后选择第二位，但是还有一个问题，如果有两个人并列薪水第一，那么你选择的第二其实是薪水第三多的人，所以其实得看一下数据集再确定，很明显，牛客网给定的数据集只有一个第一，所以怎么写都行。

18.查找当前薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

题目描述：
查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

#wasrehpic的答案
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name 
FROM employees AS e INNER JOIN salaries AS s 
ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

19.查找所有员工的last_name和first_name以及对应的dept_name

题目描述:
查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (dept_no));
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

SELECT emp.last_name, emp.first_name, dep.dept_name
FROM employees AS emp LEFT JOIN dept_emp AS dept ON emp.emp_no = dept.emp_no
LEFT JOIN departments AS dep ON dept.dept_no = dep.dept_no

注：多表连接（超过两个表）

20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

题目描述:
查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

SELECT MAX(salary)-MIN(salary) AS growth
FROM salaries
WHERE emp_no="10001"

21.查找所有员工自入职以来的薪水涨幅情况

题目描述：
查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

思考过程：
①创建第一个表，存储现在的薪水和员工号
②创建第二个表，存储入职的薪水和员工号
③将两个表连接
④计算涨薪

#wasrehpic答案，自己没写出来
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no,s.salary 
      FROM employees e LEFT JOIN salaries s 
      ON e.emp_no = s.emp_no 
      WHERE s.to_date = '9999-01-01') AS sCurrent
INNER JOIN(SELECT s.emp_no,s.salary 
           FROM employees e LEFT JOIN salaries s 
           ON e.emp_no = s.emp_no 
           WHERE s.from_date = e.hire_date) AS sStart
ON sCurrent.emp_no = sStart.emp_no
ORDER BY growth

22.统计各个部门对应员工涨幅的次数总和

题目描述：
统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (dept_no));
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

SELECT dep.dept_no,dep.dept_name,COUNT(dep.dept_no)AS sum
FROM departments dep INNER JOIN dept_emp dept ON dep.dept_no=dept.dept_no
INNER JOIN salaries s ON s.emp_no=dept.emp_no
GROUP BY dep.dept_no

23.对所有员工的薪水按照salary进行按照1-N的排名

题目描述: 对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列 CREATE TABLEsalaries(emp_noint(11) NOT NULL,salaryint(11) NOT NULL,from_datedate NOT NULL,to_datedate NOT NULL, PRIMARY KEY (emp_no,from_date));

没写出来
答案如下

Image.png

24.获取所有非manager员工当前的薪水情况

题目描述：
获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

考虑思路：
①将dept_emp和salaries连接，salaeies中就有所有的员工名单，所以不用employees。
②过滤掉经理

SELECT de.dept_no, s.emp_no, s.salary 
FROM dept_emp AS de INNER JOIN salaries AS s 
ON s.emp_no = de.emp_no 
AND s.to_date = '9999-01-01'
WHERE de.emp_no NOT IN (SELECT emp_no 
                        FROM dept_manager 
                        WHERE to_date = '9999-01-01')