收藏    

MySQL 数据库根据一个或多个字段查询重复数据

1、根据一个字段查找重复数据

比如，User 表中的 userName 需要保持唯一，查询重复数据的语句如下：

SELECT userName from User GROUP BY userName HAVING count(*) > 1;

2、根据多个字段查找重复数据　　比如，User 表中的 userName、loginType 需要保持数据的唯一性，查询重复数据的语句如下：

SELECT * FROM User GROUP BY userName,loginType HAVING count( * ) > 1;

SELECT distinct userName,loginType FROM User 等于

SELECT * FROM User GROUP BY userName,loginType HAVING count( * ) = 1; + SELECT * FROM User GROUP BY userName,loginType HAVING count( * ) > 1;