【MySQL】十三、关于查询结果的去重（distinct）

1. distinct使用案例：

select job from emp;

查询结果：

+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+
14 rows in set (0.00 sec)

我们发现查询结果中出现了很多重复的记录。如果想要去去掉重复的记录。
这个时候，我们只需要在select后面加一个distinct关键字

select distinct job from emp;

查询结果：

+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

distinct（明显的，独特的，清楚的，有区别的）

2. distinct 只能出现在所有字段的最前面

select ename, distinct job from emp; // 错误！

以上sql语句是错误的，distinct只能出现在所有字段的最前面。

3. 多字段联合去重

select deptno, job from emp order by deptno;

我们首先查询一下emp表中的部门和工作岗位。

+--------+-----------+
| deptno | job       |
+--------+-----------+
|     10 | MANAGER   |
|     10 | PRESIDENT |
|     10 | CLERK     |
|     20 | CLERK     |
|     20 | MANAGER   |
|     20 | ANALYST   |
|     20 | CLERK     |
|     20 | ANALYST   |
|     30 | SALESMAN  |
|     30 | SALESMAN  |
|     30 | SALESMAN  |
|     30 | MANAGER   |
|     30 | SALESMAN  |
|     30 | CLERK     |
+--------+-----------+
14 rows in set (0.00 sec)

通过查询，我们发现不同部门间存在重复的岗位。

select distinct deptno, job from emp;

联合deptno, job两个字段去重

+--------+-----------+
| deptno | job       |
+--------+-----------+
|     10 | CLERK     |
|     10 | MANAGER   |
|     10 | PRESIDENT |
|     20 | ANALYST   |
|     20 | CLERK     |
|     20 | MANAGER   |
|     30 | CLERK     |
|     30 | MANAGER   |
|     30 | SALESMAN  |
+--------+-----------+
9 rows in set (0.00 sec)

4. 案例：统计岗位的数量[count(distinct …)的使用 ]

select count(distinct job) from emp;

查询结果：

+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.01 sec)