mysql去重字段_MySQL去重distinct

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

语法:

select distinct [,...,] 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 [,...,]) from ;

示例:

mysql> select count(distinct deptno,job) from emp;

+----------------------------+

| count(distinct deptno,job) |

+----------------------------+

| 9 |

+----------------------------+

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值