一、获取每个部门中当前员工薪水最高的相关信息
有一个员工表dept_emp简况如下:
有一个薪水表salaries简况如下:
获取每个部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号dept_no升序排列,以上例子输出如下:
(注意: Mysql与Sqlite select 非聚合列的结果可能不一样)
【解题思路】:由于使用GROUP BY函数时,需要将select后(除了聚合函数外的)所有内容作其分组依据,因此不能简单使用连接函数+group by函数。
以下答案中考虑使用子查询,先查询出两个独立的表,再对这两个新表进行连接处理。
SELECT t1.dept_no, t1.emp_no, t2.max_salary
FROM
(
SELECT e.emp_no , e.dept_no , s.salary
FROM dept_emp e
JOIN salaries s
ON e.emp_no=s.emp_no
AND e.to_date='9999-01-01'
AND s.to_date='9999-01-01'
) t1
JOIN
(
SELECT e.dept_no , MAX(salary) max_salary
FROM dept_emp e
JOIN salaries s
ON e.emp_no=s.emp_no
AND e.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY e.dept_no
) t2
ON t1.dept_no=t2.dept_no
WHERE t1.salary=t2.max_salary
ORDER BY t2.dept_no
二、查找在职员工自入职以来的薪水涨幅情况
有一个员工表employees简况如下:
有一个薪水表salaries简况如下:
请你查找在职员工自入职以来的薪水涨幅情况,给出在职员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序,以上例子输出为
(注: to_date为薪资调整某个结束日期,或者为离职日期,to_date='9999-01-01'时,表示依然在职,无后续调整记录)
【解题思路】:要求‘在职’员工,因此可以确定最后工资时间to_date为9999-01-01,入职以来涨幅 = hire_date.salary - 9999-01-01.salary(不能直接用max(salary) - min(salary),由于并不能保证该员工工资是一直上升的)。因此与上题类似,需要制作出两张单独的表格,即:入职时工资表、9999-01-01时工资表,二者相减即可!
select e.emp_no, s2.salary - s.salary growth
from employees e join salaries s
on e.emp_no = s.emp_no
join salaries s2
on s.emp_no = s2.emp_no
where s2.to_date = '9999-01-01'
and s.from_date = e.hire_date
order by growth asc;
三、 获取员工其当前的薪水比其manager当前薪水还高的相关信息
有一个,部门关系表dept_emp简况如下:
有一个部门经理表dept_manager简况如下:
有一个薪水表salaries简况如下:
获取员工其当前的薪水比其manager当前薪水还高的相关信息,
第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
以上例子输出如下:
【解题思路】:职工找到职工工资表,老板找到老板工资表(salaries分为两次使用)。
所谓连接,其实就是将excel里根据连接条件形成更多的列数,可以现在脑袋里想一想连接后的情形,结果就出来了。
select de.emp_no, mager.emp_no manager_no, s.salary emp_salary, s2.salary manager_salary
from dept_emp de join dept_manager mager #职员找到自己的老板
on de.dept_no = mager.dept_no
join salaries s ##普通职员工资
on de.emp_no = s.emp_no
join salaries s2 ##老板们的工资
on mager.emp_no = s2.emp_no
where s.salary > s2.salary;