mysql 重复数据 distinct_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 | 拉萨 |

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