获取每个部门中当前员工薪水最高的相关信息
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 dept_emp VALUES(10004,'d002','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','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');
INSERT INTO salaries VALUES(10004,88958,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10005,90000,'2001-08-02','9999-01-01');
SELECT * from dept_emp;
//1 sqlite
SELECT d.dept_no, d.emp_no, s.salary
FROM dept_emp d
INNER JOIN salaries s
ON d.emp_no=s.emp_no
WHERE s.salary in
(
SELECT MAX(s2.salary)
FROM dept_emp d2
INNER JOIN salaries s2
ON d2.emp_no=s2.emp_no
AND d2.dept_no = d.dept_no
)
ORDER BY d.dept_no;
or
SELECT d.dept_no, d.emp_no, s.salary
FROM dept_emp d
INNER JOIN salaries s
ON d.emp_no=s.emp_no
WHERE s.salary in
(
SELECT MAX(s2.salary)
FROM dept_emp d2
INNER JOIN salaries s2
ON d2.emp_no=s2.emp_no
where d2.dept_no = d.dept_no
)
ORDER BY d.dept_no;
//2 hql
with b as (select d.emp_no,salary,dept_no
from dept_emp d
left join salaries s on d.emp_no=s.emp_no),
a as(select dept_no,max(salary) maxSalary
from b group by dept_no)
select a.dept_no,b.emp_no,a.maxSalary
from a
left join b
on a.dept_no=b.dept_no
and b.salary=a.maxsalary
order by a.dept_no asc;
//3 mysql
select a.dept_no,b.emp_no,a.maxSalary
from
(select dept_no,max(salary) maxSalary
from (select d.emp_no,salary,dept_no
from dept_emp d
left join salaries s
on d.emp_no=s.emp_no)b group by dept_no)a
left join
(select d.emp_no,salary,dept_no
from dept_emp d
left join salaries s
on d.emp_no=s.emp_no)b
on a.dept_no=b.dept_no
and b.salary=a.maxsalary
order by a.dept_no asc;
