题目描述
获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
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
));
输入描述:
解题思路
题目的核心是考察GROUP BY
语句分组和JOIN
连接。需要先对两个表进行连接,然后分组进行处理。
AC代码
SELECT d.dept_no, d.emp_no, MAX(salary) FROM
dept_emp AS d JOIN salaries AS s ON d.emp_no = s.emp_no
WHERE (
d.to_date = '9999-01-01' AND
s.to_date = '9999-01-01'
) GROUP BY d.dept_no