mysql重复数据查询

假设有表test

mysql> select * from test;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | a    | f    |
|  2 | b    | f    |
|  3 | a    | e    |
|  4 | b    | e    |
|  5 | c    | e    |
|  6 | d    | e    |
+----+------+------+
6 rows in set (0.00 sec)

 

现在要找出name列有重复的数据

mysql> select name from test group by name having count(name)>1;
+------+
| name |
+------+
| a    |
| b    |
+------+
2 rows in set (0.00 sec)

 

如果还要求显示name外其他数据

mysql> select * from test where name in (select name from test group by name having count(name)>1);
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | a    | f    |
|  2 | b    | f    |
|  3 | a    | e    |
|  4 | b    | e    |
+----+------+------+
4 rows in set (0.00 sec)

 

或者要求显示重复次数

mysql> select count(name) as 'number',name from test group by name having count(name)>1;
+--------+------+
| number | name |
+--------+------+
|      2 | a    |
|      2 | b    |
+--------+------+
2 rows in set (0.00 sec)

 

或者要求显示重复数据中id最小的那一个

mysql> select min(id),name from test group by name having count(name)>1;
+---------+------+
| min(id) | name |
+---------+------+
|       1 | a    |
|       2 | b    |
+---------+------+
2 rows in set (0.00 sec)