MySQL - 13使用DISTINCT去除重复数据

distinct的使用

distinct用于在查询中返回列的唯一不同值(去重复)，支持单列或多列。在实际的应用中，表中的 某一列含有重复值是很常见的，如employ表的dept列。如果在查询数据时，希望得到某列的所有不同值，可以使用distinct。

#distinct语法
SELECT DISTINCT column_name, column_name FROM table_name;

select distinct sex from employee;

实战案例

初始化表数据

create table footprint(
	id int not null auto_increment primary key,
	username varchar(30),
	city varchar(30),
	visit_date varchar(10)
);

insert into footprint(username, city, visit_date) values('liufeng', 'guiyang', '2019-12-05');
insert into footprint(username, city, visit_date) values('liufeng', 'guiyang', '2020-01-15');
insert into footprint(username, city, visit_date) values('liufeng', 'baijing', '2018-10-10');
insert into footprint(username, city, visit_date) values('zhangsan', 'shanghai', '2020-01-01');
insert into footprint(username, city, visit_date) values('zhangsan', 'shanghai', '2020-02-02');
insert into footprint(username, city, visit_date) values('lisi', 'lasa', '2016-12-20');

查看表数据

mysql> select * from footprint;
+----+----------+----------+------------+
| id | username | city     | visit_date |
+----+----------+----------+------------+
|  1 | liufeng  | guiyang  | 2019-12-05 |
|  2 | liufeng  | guiyang  | 2020-01-15 |
|  3 | liufeng  | baijing  | 2018-10-10 |
|  4 | zhangsan | shanghai | 2020-01-01 |
|  5 | zhangsan | shanghai | 2020-02-02 |
|  6 | lisi     | lasa     | 2016-12-20 |
+----+----------+----------+------------+
6 rows in set (0.00 sec)

distinct的使用

mysql> select distinct username from footprint;
+----------+
| username |
+----------+
| liufeng  |
| zhangsan |
| lisi     |
+----------+
3 rows in set (0.00 sec)

mysql> select distinct city from footprint;
+----------+
| city     |
+----------+
| guiyang  |
| baijing  |
| shanghai |
| lasa     |
+----------+
4 rows in set (0.01 sec)

mysql> select distinct username, city from footprint;
+----------+----------+
| username | city     |
+----------+----------+
| liufeng  | guiyang  |
| liufeng  | baijing  |
| zhangsan | shanghai |
| lisi     | lasa     |
+----------+----------+
4 rows in set (0.00 sec)