在 MySQL 中查找并显示重复记录?
mysqlmysqli database更新于 2024/1/24 18:39:00
首先，借助 CREATE 命令创建一个表。给出如下 −
mysql> CREATE table DuplicateFound -> ( -> ID int, -> Name varchar(100), -> Location varchar(200) -> ); Query OK, 0 rows affected (0.45 sec)
创建表后，通过INSERT命令插入记录，如下 −
mysql> INSERT into DuplicateFound values(1,'John','US'); Query OK, 1 row affected (0.10 sec) mysql> INSERT into DuplicateFound values(2,'Bob','UK'); Query OK, 1 row affected (0.18 sec) mysql> INSERT into DuplicateFound values(3,'David','US'); Query OK, 1 row affected (0.14 sec) mysql> INSERT into DuplicateFound values(4,'Smith','US'); Query OK, 1 row affected (0.16 sec) mysql> INSERT into DuplicateFound values(5,'Carol','UK'); Query OK, 1 row affected (0.16 sec)
借助 SELECT 语句显示记录。这在下面给出 −
mysql> SELECT * from DuplicateFound;
The following is the output obtained −
+------+-------+----------+ | 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)
