salary.md

# 工资最高的人

现有员工信息表如下：

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

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

<hr/>

点击进入[MySQL实战练习环境](https://mydev.csdn.net/product/pod/new?image=cimg-centos7-skilltreemysql&connect=auto&create=auto&utm_source=skill){target="_blank"}。

* `show databases;` 列出所有数据库
* `show tables;` 列出所有表

## 答案

```sql
select id, name, dept, salary
from (select id, 
             name, 
             dept, 
             salary, 
             rank() over (
                 partition by dept 
                 order by salary desc) as r
      from employee) as t
where r <= 5;
```

## 选项

### 结构错误

```sql
select id, name, dept, max(salary) as salary
from employee
group by dept
having id < 6;
```

### 结构错误

```sql
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
where count(r.id) <= 5;
```

### 结构错误

```sql
select l.id, l.name, l.dept, l.salary
from employee as l
         join (
         select max(salary, 5) as salary, dept
         from employee
         group by dept) as r
         on l.dept = r.dept and l.salary = r.salary
```

### 结构错误

```sql
select id, 
       name, 
       dept, 
       salary, 
       rank() over (
           partition by dept 
           order by salary desc) as r
from employee
where r <= 5;
```

### 结构错误

```sql
select id, 
       name, 
       dept, 
       salary, 
       rank() as r over (
            partition by dept 
            order by salary desc)
from employee
where r <= 5;
```