【牛客】SQL206 获取每个部门中当前员工薪水最高的相关信息

1、描述

有一个员工表dept_emp简况如下：

有一个薪水表salaries简况如下：

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列，以上例子输出如下:

2、题目建表

drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,92527,'2001-08-02','9999-01-01');

3、答案

3.1 每个部门最高的薪水

select d.dept_no,max(s.salary) salary 
from dept_emp d,salaries s
where d.emp_no=s.emp_no
group by d.dept_no;

3.2 (将员工、部门、薪水整合在一张表内)

select d.emp_no,d.dept_no,s.salary 
from dept_emp d,salaries s
where d.emp_no=s.emp_no;

3.3(将t1和t2整合)

select t1.dept_no,t2.emp_no,t1.salary 
from (表t1) t1
join (表t2) t2
on t1.dept_no=t2.dept_no and t1.salary=t2.salary
order by t1.dept_no;

select t1.dept_no,t2.emp_no,t1.salary
from (select d.dept_no,max(s.salary) salary from dept_emp d,salaries swhere d.emp_no=s.emp_nogroup by d.dept_no) t1
join (select d.emp_no,d.dept_no,s.salary from dept_emp d,salaries swhere d.emp_no=s.emp_no) t2
on t1.dept_no = t2.dept_no and t1.salary=t2.salary
order by t1.dept_no;