描述
有一个员工表dept_emp简况如下:
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 |
有一个薪水表salaries简况如下:
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以及其对应的salary,按照部门编号dept_no升序排列,以上例子输出如下:
(注意: Mysql与Sqlite select 非聚合列的结果可能不一样)
dept_no | emp_no | maxSalary |
d001 | 10001 | 88958 |
d002 | 10003 | 92527 |
示例1
输入:
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');
复制输出:
d001|10001|88958 d002|10003|92527
select dept_no,s.emp_no,salary maxSalary
from dept_emp de join salaries s on de.emp_no=s.emp_no
where (dept_no,salary) in (select dept_no,max(salary)
from dept_emp de join salaries s
on de.emp_no=s.emp_no
where s.to_date='9999-01-01' and de.to_date='9999-01-01'
group by dept_no)
order by dept_no