获取所有部门中当前员工薪水最高的相关信息,给出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 d.dept_no将每个部门分为一组,用max()函数选取每组中工资最高者】
select d.dept_no,s.emp_no,max(s.salary) as salary from salaries s,dept_emp d where d.emp_no=s.emp_no and s.to_date='9999-01-01' and d.to_date='9999-01-01' group by d.dept_no