牛客题霸 11-20

具体题目参考链接：https://www.nowcoder.com/ta/sql

---

11. 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no

思路：

• 一个部门对应一个manager，所以用 e.dept_no = m.dept_no 为条件连接两表，可以得到每个员工对应部门的manager是谁
• 如果当前的manager是自己的话结果不显示，所以需要判断 e.emp_no <> m.emp_no
• 注意两个“当前”，即 e.to_date = ‘9999-01-01’ and m.to_date = ‘9999-01-01’

select e.emp_no, m.emp_no as manager
from dept_emp as e join dept_manager as m
on e.dept_no = m.dept_no
where e.emp_no != m.emp_no and e.to_date = '9999-01-01' and m.to_date = '9999-01-01';

---

12. 获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，结果按照部门升序排列

推荐阅读：如何正确理解SQL关联子查询

思路一：用相关子查询，子查询每次仅对一个部门内的员工进行处理。首先，子查询找到父查询中第一个部门最高的salary，然后，父查询找到该部门中所有薪水等于该最高salary的员工，接着子查询继续寻找下一个部门的最高salary，重复循环。

select de.dept_no, de.emp_no, s.salary
from dept_emp de join salaries s
on de.emp_no = s.emp_no and de.to_date = '9999-01-01' and s.to_date = '9999-01-01'
where s.salary = (select max(s2.salary)
              from dept_emp de2 join salaries s2
              on de2.emp_no = s2.emp_no and de2.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
              where de2.dept_no = de.dept_no)
order by de.dept_no

思路二：用窗口函数，先在子查询中对组内的员工薪水进行排名。然后在父查询中选择 rank 为1的员工。

tips：窗口函数是在 WHERE 子句之后执行，所以必须要先把窗口函数的 SELECT 作为子查询，然后在父查询中写 WHERE a.rank = 1。假如只用一层查询，会报错。

SELECT a.dept_no, a.emp_no, a.salary AS maxSalary
FROM (
    SELECT d.emp_no, d.dept_no, s.salary,
    DENSE_RANK() OVER (PARTITION BY d.dept_no ORDER BY s.salary DESC) AS rank
    FROM dept_emp d, salaries s
    WHERE d.emp_no = s.emp_no
) a
WHERE a.rank = 1;

下面是第一种错误解法，MAX(SALARY) 和 emp_no 不一定对应！因为 GROUP BY 默认取非聚合的第一条记录。

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

下面是第二种错误解法，如果不用相关子查询，直接用 where s.salary in，会出现：B部门的最高薪水在A部门中也出现了，但却不是A部门的最高薪水，则下面这种解法会把A部门中该薪水对应的员工也查询出来。

select de.dept_no, de.emp_no, s.salary
from dept_emp de join salaries s
on de.emp_no = s.emp_no and de.to_date = '9999-01-01' and s.to_date = '9999-01-01'
where s.salary in (select max(s2.salary)
              from dept_emp de2 join salaries s2
              on de2.emp_no = s2.emp_no and de2.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
              group by de2.dept_no)
order by de.dept_no;

---

13. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t

思路：使用 group by 将title分组，再用 having 子句找出大于等于2的组，having 必须在 group by 之后，并且有 group by 才能使用 having。

select title, count(emp_no) as t
from titles
group by title
having t >= 2;

---

14. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略

思路：使用 distinct 进行去重

select title, count(distinct emp_no) as t
from titles
group by title
having t >= 2;

---

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

思路：员工号为奇数，则emp_no取余应为1，last_name不为Mary，用‘!=’表示，另外注意字符串要用单括号括起来，逆序用desc。

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

---

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

思路：对两表进行内连接，然后按 title 分组。

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

---

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

思路一：子查询中，使用 group by 对 salary 去重，然后用 order by 排序，从而找到排名第二的薪水数 salary，然后在主查询中，查询所有等于该 salary 的员工信息。

limit m, n：表示从下标m （下标从0开始）开始，取 n 条数据

select emp_no, salary
from salaries
where salary = (
    select salary
    from salaries
    group by salary
    order by salary desc
    limit 1, 1
);

下面是错误解法，要考虑到同一薪水有多人，例如薪水最高的有3人；薪水第二高的有2人

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

思路二：
先在工资表里找出最多工资，再以工资小于最大工资为限定条件，即salary < (select max(salary)可以得到排名第二的工资数。
再使用max(salary)在 除去排名第一工资 的表里找最大的工资，即找到排名第二的工资。

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

---

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

思路：本题在上一题的思路二的基础上，多了个内连接。

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

---

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

思路：关键就是要理清三个表之间的关系。题目要求包括暂时没有分配部门的员工，首先将 employees 作为主表，与 dept_emp 左连接，得到每个员工的部门号。然后以上一步得到的结果表作为主表，与 departments 左连接，得到每个部门号的 dept_name

select e.last_name, e.first_name, dp.dept_name
from (employees as e left join dept_emp as de on e.emp_no = de.emp_no)
left join departments as dp
on de.dept_no = dp.dept_no;