查询重复数据的几种方式

数据库内容如下图所示（表的名称为stu）

1.列出重复的名字

SELECT NAME FROM stu GROUP BY NAME HAVING COUNT(id)>1

2.列出名字重复的所有记录

方法一：

SELECT * FROM stu WHERE NAME IN(SELECT NAME FROM stu GROUP BY NAME HAVING COUNT(id)>1)

方法二：

SELECT s1.* FROM stu s1 WHERE (SELECT COUNT(s2.`id`) FROM stu s2 WHERE s1.`name`=s2.`name`) >1

方法三：

SELECT s1.* FROM stu s1 WHERE EXISTS (SELECT s2.* FROM stu s2 WHERE s2.`name`=s1.`name` AND s2.`id`<>s1.`id`)

方法四：

SELECT s1.* FROM stu s1 INNER JOIN stu s2 ON s1.`name`=s2.`name` AND s1.`id`<>s2.`id` GROUP BY s1.id