大家好,我是天空之城,今天给大家带来,用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 | 拉萨 |
+----------+------+