MySQL去重distinct

去重

在MySQL中需要查询表中不重复的记录时，可以使用distinct关键字过滤重复记录。

语法：

select distinct <字段名1>[,<字段名2>...,<字段名n>] from <表名>; 数据表如下：

mysql> select * from emp; +-------+--------+------------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+------------+------+------------+------+------+--------+ | 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | ward | salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7566 | jones | manager | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | clark | manager | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | scott | analyst | 7566 | 1987-04-19 | 3000 | NULL | 20 | | 7839 | king | persident | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7844 | turner | salesman | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7876 | adams | clerk | 7788 | 1987-05-23 | 1100 | NULL | 20 | | 7900 | james | clerk | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | ford | analyst | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | miller | clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 | +-------+--------+------------+------+------------+------+------+--------+ 示例1：单个字段去重

mysql> select distinct deptno from emp; +--------+ | deptno | +--------+ | 20 | | 30 | | 10 | +--------+ mysql> select distinct job from emp; +------------+ | job | +------------+ | clerk | | salesman | | manager | | analyst | | persident | +------------+ 示例2：多个字段去重

mysql> select distinct deptno,job from emp; +--------+------------+ | deptno | job | +--------+------------+ | 20 | clerk | | 30 | salesman | | 20 | manager | | 30 | manager | | 10 | manager | | 20 | analyst | | 10 | persident | | 30 | clerk | | 10 | clerk | +--------+------------+ 多个字段去重时，distinct关键字必须位于第一个字段前，多个字段完全一样的情况下，才会过滤。

非重复计数：

select count(distinct <字段名1>[,<字段名2>...,<字段名n>]) from <表名>; 示例：

mysql> select count(distinct deptno,job) from emp; +----------------------------+ | count(distinct deptno,job) | +----------------------------+ | 9 | +----------------------------+