MySQL | DQL语句-distinct去重

约 434 字大约 1 分钟

2025-08-06

查询工作岗位

SQL
select job from emp;

SQL
mysql> 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)

可以看到工作岗位中有重复的记录，如何在显示的时候去除重复记录呢？在字段前添加distinct关键字。

SQL
select distinct job from emp;

SQL
mysql> select distinct job from emp; 

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

注意：这个去重只是将显示的结果去重，原表数据不会被更改。

接下来测试一下，在distinct关键字前添加其它字段是否可以？

SQL
select ename, distinct job from emp;

分析一下：ename是14条记录，distinct job是5条记录，可以同时显示吗？

SQL
mysql> select ename, distinct job from emp;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct job from emp' at line 1

报错了，通过测试得知，distinct只能出现在所有字段的最前面。当distinct出现后，后面多个字段一定是联合去重的，我们来做两个练习就知道了：

练习1：找出公司中所有的工作岗位。

SQL
select distinct job from emp;

SQL
mysql> select distinct job from emp;

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

练习2：找出公司中不同部门的不同工作岗位。

SQL
select distinct deptno,job from emp;

SQL
mysql> select distinct deptno,job from emp;

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