题目22
SQL22 统计各个部门的工资记录数
思路其实很简单
- 三张表的两两连结 RIGHT/LEFT join
- 分组group by,排序order by即可,ASC升序 DESC降序
- 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
思路呢,感觉也很明确
- 根据部门表和部门管理者表,找到员工(非管理者)
- 然后再连结部门表和薪资表
- 筛选出员工的薪资就可以了啊
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
思路:
思路其实蛮清晰的
- 找到部门经理和部门员工的对应关系,并显示出来
- 将其薪资显示出来
- 获取薪资倒挂的员工
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升序排序
思路:
- 二话不说,连结三张表
- 分组再排序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;