查找并显示MySQL中的重复记录？
首先，借助CREATE命令创建一个表。这给出如下-
mysql> CREATE table DuplicateFound -> ( -> ID int, -> Name varchar(100), -> Location varchar(200) -> );
创建表后，将在INSERT命令的帮助下插入记录，如下所示：
mysql> INSERT into DuplicateFound values(1,'John','US'); mysql> INSERT into DuplicateFound values(2,'Bob','UK'); mysql> INSERT into DuplicateFound values(3,'David','US'); mysql> INSERT into DuplicateFound values(4,'Smith','US'); mysql> INSERT into DuplicateFound values(5,'Carol','UK');
在SELECT语句的帮助下显示记录。这在下面给出-
mysql> SELECT * from DuplicateFound;
以下是获得的输出-
+------+-------+----------+ | ID | Name | Location | +------+-------+----------+ | 1 | John | US | | 2 | Bob | UK | | 3 | David | US | | 4 | Smith | US | | 5 | Carol | UK | +------+-------+----------+ 5 rows in set (0.00 sec)
查找和一起显示重复记录的查询如下：
mysql> SELECT * from DuplicateFound -> where location in (select location from DuplicateFound group by location having count(location) >1 ) -> order by location;
以下是获得的输出
+------+-------+----------+ | ID | Name | Location | +------+-------+----------+ | 2 | Bob | UK | | 5 | Carol | UK | | 1 | John | US | | 3 | David | US | | 4 | Smith | US | +------+-------+----------+ 5 rows in set (0.06 sec)
我们可以显示属于单个位置的那些记录。这给出如下-
mysql> select Name,location from DuplicateFound -> group by location -> having count(location) > 1;
以下是输出
+------+----------+ | Name | Location | +------+----------+ | John | US | | Bob | UK | +------+----------+ 2 rows in set (0.04 sec)