in_column.md

# 列子查询

Joe 打算写一个查询，根据员工表

```sql
create table employee
(
    id   int primary key auto_increment,
    dept varchar(64),
    name varchar(256),
    post varchar(16)
)
```

统计每个部门的人数，但是他不打算用 group by。那么还可以怎做？

<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 distinct(dept)                  as dept,
               (select count(*)
                from employee as i
                where i.dept = o.dept) as emp
from employee as o;
```

## 选项

### A

```sql
select distinct(dept)               as dept,
               (select count(*)
                from employee as i) as emp
from employee as o;
```

### B

```sql
select distinct(dept)              as dept,
               (select count(*)
                from employee
                where dept = dept) as emp
from employee
```

### C

```sql
select dept                    as dept,
       (select count(*)
        from employee as i
        where i.dept = o.dept) as emp
from employee as o
```