any.md

# ANY

Joe 想要从员工表

```sql
create table employee(
    id int primary key auto_increment,
    name varchar(256),
    dept varchar(64),
    salary decimal(12, 4)
);
```

构造一个员工列表，排除每个部门最高工资的员工。这个查询可以怎样写？

<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 employee as o
where o.salary < any(select salary 
                     from employee as i 
                     where i.dept=o.dept)
```

## 选项

### A

```sql
select id, name, dept, salary
from employee as o
join employee as i on 
    o.dept = i.dept and o.salary < i.salary
```

### B

```sql
select o.id, o.name, o.dept, o.salary
from employee as o
left join employee as i on 
    o.dept = i.dept and o.salary < i.salary
where i.id is null;
```

### C

```sql
select o.id, o.name, o.dept, o.salary 
from employee as o 
    left join employee as i 
        on o.dept = i.dept and o.salary < i.salary 
where i.id is not null;
```