2021-04-20 09:55 已编辑门头沟学院数据分析师

关注

1. 人力资源数据（SQL1~SQL26）

SQL1 查找最晚入职员工的所有信息
查找employees里最晚入职员工的所有信息

   SELECT *
  FROM employees
  WHERE hire_date =
    (SELECT MAX(hire_date)
    FROM employees);

SQL2 查找入职员工时间排名倒数第三的员工所有信息
请你查找employees里入职员工时间排名倒数第三的员工所有信息
①考虑去重：

  select * from employees
  where hire_date=(
      select distinct hire_date from employees
      order by hire_date DESC
      limit 2,1);

②默认用美式排名（即用同名次的情况时，后续的名次会跳过），所以不加 DISTINCT 去重
法2. 计数法：

思路："第N高" → "比它高的值的个数 + 1 = N"

SELECT *
FROM employees a
WHERE (
    SELECT COUNT(*) + 1
    FROM employees 
    WHERE hire_date > a.hire_date
) = 3;

SQL3 查找各个部门当前领导当前薪水详情以及其对应部门编号
查找各个部门当前领导薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序

SELECT s.*, d.dept_no
FROM salaries s
JOIN dept_manager d USING(emp_no)
WHERE s.to_date='9999-01-01' AND d.to_date='9999-01-01'
ORDER BY s.emp_no;

注意：找的是当前领导，即在职的领导，所以要加上连接条件 s.to_date = '9999-01-01' AND d.to_date = '9999-01-01' （当然也可以写在 WHERE 里作为连接后的筛选条件，但从性能上讲写在 ON 里好一点）

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

SELECT
    last_name,
    first_name,
    dept_no
FROM employees 
INNER JOIN dept_emp USING(emp_no);

SQL5 查找所有员工的last_name和first_name以及对应部门编号dept_no
请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工

SELECT last_name, first_name, d.dept_no
FROM employees e
LEFT JOIN dept_emp d USING(emp_no);

SQL7 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
思路："超过15次" ：很明显是要按员工分组统计并用 HAVING 进行分组后筛选

SELECT 
    emp_no,
    COUNT(*) t
FROM salaries 
GROUP BY emp_no
HAVING COUNT(*) > 15;
-- MySQL 里的话可以直接用列别名 t > 15

SQL8 找出所有员工当前具体的薪水salary情况
请你找出所有员工当前（(to_date=‘9999-01-01’)）的薪水salary情况，对于相同的薪水只显示一次，并按照逆序显示

SELECT DISTINCT salary
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary DESC;

SQL10 获取所有非manager的员工emp_no
有两种方法：
法1. LEFT JOIN + IS NULL

SELECT e.emp_no
FROM employees e
LEFT JOIN dept_manager d USING(emp_no)
WHERE d.dept_no IS NULL;

法2. 子查询 + NOT IN

SELECT emp_no
FROM employees 
WHERE emp_no NOT IN(
    SELECT emp_no
    FROM dept_manager
);

SQL11 获取所有员工当前的manager
获取所有的当前员工和员工对应的经理，如果员工本身是经理的话则不显示

SELECT d.emp_no, dm.emp_no AS manager
FROM dept_emp d
JOIN dept_manager dm USING(dept_no)
WHERE d.emp_no!=dm.emp_no AND 
      e.to_date='9999-01-01'AND 
      dm.to_date='9999-01-01'
-- 不等于符号 <> 或 !=

☆☆SQL12 获取所有部门中当前员工薪水最高的相关信息
获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列
法1. 子查询

SELECT dept_no, d1.emp_no, salary AS maxSalary
FROM dept_emp d1
JOIN salaries s1 USING(emp_no)
WHERE d1.to_date='9999-01-01'AND 
     s1.to_date='9999-01-01'AND
     salary in(
     SELECT MAX(salary)
     FROM salaries s2
     JOIN dept_emp d2 USING(emp_no)
     WHERE d1.dept_no=d2.dept_no AND
           d2.to_date='9999-01-01'AND 
           s2.to_date='9999-01-01'
)
ORDER BY d1.dept_no;

法2. 用窗口函数里的排序函数
待学
补充：如果这题不需要给出emp_no(即只求所有部门中当前员工薪水最高值)，则用INNER JOIN和GROUP BY和MAX即可解决：

SELECT dept_no, d.emp_no, 
       MAX(salary) AS maxSalary
FROM dept_emp d
JOIN salaries s USING(emp_no)
WHERE d.to_date='9999-01-01'AND 
      s.to_date='9999-01-01'
GROUP BY dept_no
ORDER BY dept_no;

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

SELECT *
FROM employees 
WHERE 
    emp_no % 2 = 1
    -- 也可写作 emp_no MOD 2 = 1
    AND last_name != 'Mary'
ORDER BY hire_date DESC;

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

SELECT 
    title,
    AVG(s.salary)
FROM titles t
INNER JOIN salaries s
ON t.emp_no = s.emp_no
AND t.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
GROUP BY title
ORDER BY AVG(s.salary);

☆SQL17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
思路：类似第2题，属于第 N 值问题里的一般情况，可以用排序提取法、计数法或用窗口函数中的排序函数解决
排序提取法：薪水第2高的员工 → 薪水等于第2高的薪水的员工

SELECT
    emp_no,
    salary
FROM salaries
WHERE salary = (
    SELECT salary
    FROM salaries
    ORDER BY salary DESC
    LIMIT 1, 1
);

计数法：薪水第2高 → 薪水比他高的只有1人

SELECT
    emp_no,
    salary
FROM salaries s
WHERE (
    SELECT COUNT(*)
    FROM salaries
    WHERE salary > s.salary
) = 1;

max()+子查询

select emp_no, salary
from salaries
where salary=(
  select max(salary)
  from salaries
  where salary <>(
     select max(salary)
     from salaries
     ));

用窗口函数中的排序函数

SELECT 
    emp_no,
    salary
FROM (
    SELECT 
        emp_no,
        salary,
        RANK() OVER(ORDER BY salary DESC) rk
    FROM salaries
) 
WHERE rk = 2;

☆SQL18 查找当前薪水排名第二多的员工编号emp_no
查找当前薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成
计数法：

SELECT 
    e.emp_no,
    s.salary,
    e.last_name,
    e.first_name
FROM salaries s
INNER JOIN employees e
ON s.emp_no = e.emp_no
AND s.to_date = '9999-01-01'
WHERE (
    SELECT COUNT(*)
    FROM salaries
    WHERE to_date = '9999-01-01'
    AND salary > s.salary
) = 1;

max()+子查询

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'

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

SELECT last_name, first_name, dept_name
FROM employees
LEFT JOIN dept_emp USING(emp_no)
LEFT JOIN departments USING(dept_no);

☆QL21 查找所有员工自入职以来的薪水涨幅情况
查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序（离职的员工 salaries 表的最新的 to_date != '9999-01-01'，这样的数据不显示在查找结果里面）
思路：工资涨幅 = 目前工资 - 入职时工资，所以要将 employees 表两次连接 salaries 表，第一次连接是为了获取入职时的工资（s.from_date = e.hire_date），第二次连接是为了获取目前的工资（to_date = ‘9999-01-01’）

SELECT 
    e.emp_no,
    s2.salary - s1.salary growth
FROM employees e
INNER JOIN salaries s1
    ON s1.emp_no = e.emp_no
    AND s1.from_date = e.hire_date
    -- 获取该员工入职时的工资
INNER JOIN salaries s2 
    ON s2.emp_no = e.emp_no
    AND s2.to_date = '9999-01-01'
    -- 获取该员工目前的工资
    -- 同时也将筛选掉离职的员工：因为离职员工没有 to_date = '9999-01-01' 的记录，但内连接只保留有匹配记录的员工
ORDER BY growth;

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

SELECT d.dept_no, d.dept_name, count(d.dept_no)
FROM departments d
JOIN dept_emp de USING(dept_no)
LEFT JOIN salaries s USING(emp_no)
GROUP BY d.dept_no
ORDER BY d.dept_no

☆SQL23 对所有员工的当前薪水按照salary进行按照1-N的排名
对所有员工当前的薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
法1：窗口函数

SELECT emp_no, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS t_rank
FROM salaries
ORDER BY t_rank,emp_no;

法2：子查询计数法
名次 = 比他高的个数 + 1
求 "比他高的个数" 时要注意两点：

要考虑到 NULL 值的情况（除非明确知道没有 NULL 值）
这里是中式排名求 "档次"，所以要加 DISTINCT 去重

SELECT
    emp_no,
    salary,
    (SELECT COUNT(DISTINCT salary) + 1
     FROM salaries
     WHERE salary > s.salary
     OR salary IS NULL AND s.salary IS NOT NULL
    ) t_rank
FROM salaries s
WHERE to_date = '9999-01-01'
ORDER BY t_rank, emp_no;

SQL24 获取所有非manager员工当前的薪水情况
法1：首选连接法

SELECT
    de.dept_no,
    de.emp_no,
    s.salary
FROM dept_emp de
JOIN dept_manager dm
    ON dm.dept_no = de.dept_no
    AND de.to_date = '9999-01-01'
    AND dm.to_date = '9999-01-01'
    AND de.emp_no <> dm.emp_no
    -- 非 manager
JOIN salaries s
    ON de.emp_no = s.emp_no
    AND s.to_date = '9999-01-01';

法2：子查询

SELECT de.dept_no, a.emp_no, s.salary
FROM  
  (SELECT e.emp_no
  FROM employees e
  LEFT JOIN dept_manager dm USING(emp_no) 
  WHERE dept_no IS NULL) a
JOIN salaries s ON s.emp_no=a.emp_no
JOIN dept_emp de on de.emp_no = a.emp_no
where s.to_date='9999-01-01';

SQL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息
获取员工其当前的薪水比其manager当前薪水还高的相关信息，第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary, 第四列给该员工对应的manager当前的薪水manager_salary
思路：能用连接法首选连接法，这道题明显是可以的
先用 dept_emp 连接 dept_manager，再两次连接 salaries 表分别获取员工和经理的工资
注意在连接条件里筛选 "当前"（to_date = ‘9999-01-01’）和 "员工比经理薪水高"（s1.salary > s2.salary）的记录
选取需要的列即可

SELECT 
    de.emp_no,
    dm.emp_no manager_no,
    s1.salary emp_salary,
    s2.salary manager_salary
FROM dept_emp de
JOIN dept_manager dm
    ON de.dept_no = dm.dept_no
    AND de.to_date = '9999-01-01'
    AND dm.to_date = '9999-01-01'
    -- "当前"
JOIN salaries s1
    ON de.emp_no = s1.emp_no
    AND s1.to_date = '9999-01-01'
JOIN salaries s2
    ON dm.emp_no = s2.emp_no
    AND s2.to_date = '9999-01-01'
    AND s1.salary > s2.salary;
    -- 员工比经理薪水高

SQL26 汇总各个部门当前员工的title类型的分配数目
汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序

SELECT 
    d.dept_no,
    d.dept_name,
    t.title,
    COUNT(t.title)
FROM departments d
JOIN dept_emp de 
     ON d.dept_no = de.dept_no
     AND de.to_date = '9999-01-01'
JOIN titles t
    ON de.emp_no = t.emp_no
    AND t.to_date = '9999-01-01'
GROUP BY d.dept_no, d.dept_name, t.title
ORDER BY dept_no;