现有员工信息表如下:
create table employee
(
id serial primary key,
name text,
dept text,
salary money
);
给出每个部门工资最高的员工的 id, name, dept, salary 四项信息:
select l.id, l.name, l.dept, l.salary
from employee as l
join (select max(salary) as salary, dept
from employee
group by dept) as r
on l.dept = r.dept and l.salary = r.salary