假設(shè)有表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)

?

現(xiàn)在要找出name列有重復(fù)的數(shù)據(jù)

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

?

如果還要求顯示name外其他數(shù)據(jù)

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)

?

或者要求顯示重復(fù)次數(shù)

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)

?

或者要求顯示重復(fù)數(shù)據(jù)中id最小的那一個(gè)

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)

?

轉(zhuǎn)載于:https://www.cnblogs.com/drizzlewithwind/p/6523436.html

總結(jié)

以上是生活随笔為你收集整理的mysql重复数据查询的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。