salary.md

# 工资最高的人

现有员工信息表如下：

```postgresql
create table employee
(
    id     serial primary key,
    name   text,
    dept   text,
    salary money
);
```

下面哪条查询，可以给出每个部门工资最高的员工的 id, name, dept, salary 四项信息？

## 答案

```postgresql
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
```

## 选项

### select 与 group by 不匹配

```postgresql
select id, name, dept, max(salary) from employee group by dept;
```

### group by 不对

```postgresql
select id, name, dept, max(salary) from employee group by dept, id, name;
```

### group by 不对

```postgresql
select id, name, dept, max(salary) from employee group by dept, id, name having salary = max(salary);
```

### 结构错误


```postgresql
select id, name, dept, max(salary) from employee where salary=max(salary) group by dept;
```