牛客网Mysql题库 22-26

题目22

SQL22 统计各个部门的工资记录数

思路其实很简单

1. 三张表的两两连结 RIGHT/LEFT join
2. 分组group by，排序order by即可，ASC升序 DESC降序
3. count（）可以计算字段出现的次数

SELECT t1.dept_no,dept_name,COUNT(dept_name) sum
FROM (
    SELECT departments.dept_no,dept_name,emp_no
    FROM departments
    RIGHT JOIN dept_emp
    ON departments.dept_no = dept_emp.dept_no
) AS t1,(
    SELECT dept_no,salary,dept_emp.emp_no
    FROM dept_emp
    RIGHT JOIN salaries
    ON salaries.emp_no = dept_emp.emp_no
) AS t2
where t1.emp_no = t2.emp_no
GROUP BY dept_no
ORDER BY dept_no ASC

题目23

SQL23 对所有员工的薪水按照salary降序进行1-N的排名

思路1：
这道题比较难
因为用的是count去统计去重后的数量，大于等于最大值的只有一个（它本身），大于等于第二大值有两个（最大值和它本身），以此类推实现排序效果

SELECT
  s1.emp_no,
  s1.salary,
  (SELECT
    COUNT(DISTINCT s2.salary)
  FROM
    salaries s2
  WHERE s2.to_date = '9999-01-01'
    AND s2.salary >= s1.salary) AS `rank`
FROM
  salaries s1
WHERE s1.to_date = '9999-01-01'
ORDER BY s1.salary DESC,
  s1.emp_no ;

思路2：
此行代码需要在mysql 8.0以上才可通过

SELECT emp_no,salary,DENSE_RANK() OVER(ORDER BY salary desc) as 'Rank' FROM salaries;

题目24

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

思路：
这道题给了四张表，但是有的表基本没用，不用care
思路呢，感觉也很明确

1. 根据部门表和部门管理者表，找到员工（非管理者）
2. 然后再连结部门表和薪资表
3. 筛选出员工的薪资就可以了啊

SELECT dept_no,dept_emp.emp_no,salary
FROM dept_emp
RIGHT JOIN salaries
ON dept_emp.emp_no = salaries.emp_no
WHERE dept_emp.emp_no in
(
    SELECT emp_no FROM dept_emp
    WHERE emp_no NOT IN (
        SElECT emp_no FROM dept_manager
    )
)

题目25

QL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息

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

思路：
思路其实蛮清晰的

1. 找到部门经理和部门员工的对应关系，并显示出来
2. 将其薪资显示出来
3. 获取薪资倒挂的员工

SELECT emp_no,manager_no,emp_salary,manager_salary
FROM (
    SELECT t1.emp_no,t1.manager_no, (
        SELECT salary FROM salaries
        WHERE emp_no = t1.emp_no
    ) AS emp_salary,(
        SELECT salary FROM salaries
        WHERE emp_no = t1.manager_no
    ) AS manager_salary
    FROM (
        SELECT dept_emp.emp_no ,dept_manager.emp_no AS manager_no
        FROM dept_manager,dept_emp
        WHERE dept_manager.dept_no = dept_emp.dept_no
    ) AS t1
) AS t2
WHERE t2.emp_salary>t2.manager_salary

题目26

SQL26 汇总各个部门当前员工的title类型的分配数目

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

思路：

1. 二话不说，连结三张表
2. 分组再排序OK

SELECT dept_emp.dept_no, dept_name, title, count(title) count
FROM dept_emp
RIGHT JOIN titles
ON dept_emp.emp_no = titles.emp_no
RIGHT JOIN departments
on dept_emp.dept_no = departments.dept_no
GROUP BY dept_no,title
ORDER BY dept_no ASC;