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

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');

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3256KB, 提交时间: 2021-07-29

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

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3264KB, 提交时间: 2021-07-25

select d.dept_no,d.emp_no,max(s.salary) as maxSalary
from dept_emp d
inner join
salaries s
on d.emp_no = s.emp_no
group by d.dept_no

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3328KB, 提交时间: 2021-07-24

select dept_no,a.emp_no,max(salary) as maxSalary
from dept_emp as a 
left join salaries as b
on a.emp_no=b.emp_no
group by dept_no

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3340KB, 提交时间: 2021-08-04

select a.dept_no,a.emp_no,b.salary
from dept_emp as a 
left join salaries as b 
on a.emp_no = b.emp_no
group by dept_no
having b.salary = max(b.salary)
order by dept_no

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3352KB, 提交时间: 2021-08-27

select e.dept_no,e.emp_no,Max(s.salary) as maxsalary
from dept_emp as e
join salaries as s
on e.emp_no = s.emp_no
GROUP by dept_no order by dept_no

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01
10003	d002	1996-08-03	9999-01-01

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	92527	2001-08-02	9999-01-01

dept_no	emp_no	maxSalary
d001	10001	88958
d002	10003	92527

详情