mysql学习笔记,用distinct去除重复数据

大家好,我是天空之城,今天给大家带来,用distinct去除重复数据,支持单列或多列

语法
SELECT DISTINCT column name, column name(会作用于两个字段,组合去重)
FROM table_ name;

还是用上次employee表格练习
mysql> select distinct sex from employee;
+------+
| sex  |
+------+
| 男   |
| 女   |
+------+

mysql> select distinct dept from employee;
+-------+
| dept  |
+-------+
| 部门A |
| 部门C |
| 部门B |
+-------+

这里建立一个新表来演示
create table footprint(
	id int not null auto_increment primary key,
	username varchar(30) comment '用户名',
	city varchar(30) comment '城市',
	visit_date varchar(10) comment '到访日期'
);

insert into footprint(username, city, visit_date) values('liusa', '贵阳', '2019-12-05');
insert into footprint(username, city, visit_date) values('liusa', '贵阳', '2020-01-15');
insert into footprint(username, city, visit_date) values('liusa', '北京', '2018-10-10');
insert into footprint(username, city, visit_date) values('zhangsan', '上海', '2020-01-01');
insert into footprint(username, city, visit_date) values('zhangsan', '上海', '2020-02-02');
insert into footprint(username, city, visit_date) values('lisi', '拉萨', '2016-12-20');

mysql> select * from footprint;
+----+----------+------+------------+
| id | username | city | visit_date |
+----+----------+------+------------+
|  1 | liusa    | 贵阳 | 2019-12-05 |
|  2 | liusa    | 贵阳 | 2020-01-15 |
|  3 | liusa    | 北京 | 2018-10-10 |
|  4 | zhangsan | 上海 | 2020-01-01 |
|  5 | zhangsan | 上海 | 2020-02-02 |
|  6 | lisi     | 拉萨 | 2016-12-20 |
+----+----------+------+------------+

对用户名去重
mysql> select distinct username from footprint;
+----------+
| username |
+----------+
| liusa    |
| zhangsan |
| lisi     |
+----------+
对城市去重
mysql> select distinct city from footprint;
+------+
| city |
+------+
| 贵阳 |
| 北京 |
| 上海 |
| 拉萨 |
+------+

对用户名,城市同时去重
mysql> select distinct username,city from footprint;
+----------+------+
| username | city |
+----------+------+
| liusa    | 贵阳 |
| liusa    | 北京 |
| zhangsan | 上海 |
| lisi     | 拉萨 |
+----------+------+



上一篇:Flink 流式聚合性能调优指南


下一篇:mysql复合索引 基数 范围搜索中索引顺序的问题?