查询数据库中的重复数据——MySQL数据库

2、插入数据

  INSERT  INTO  `t_people`  VALUES ( 1,  ‘100‘,  ‘张三‘);
 INSERT  INTO  `t_people`  VALUES ( 2,  ‘100‘,  ‘张三‘);
 INSERT  INTO  `t_people`  VALUES ( 3,  ‘100‘,  ‘张三‘);
 INSERT  INTO  `t_people`  VALUES ( 4,  ‘101‘,  ‘李四‘);
 INSERT  INTO  `t_people`  VALUES ( 5,  ‘101‘,  ‘李四‘);
 INSERT  INTO  `t_people`  VALUES ( 6,  ‘102‘,  ‘王五‘);
 INSERT  INTO  `t_people`  VALUES ( 7,  ‘103‘,  ‘赵六‘);
 INSERT  INTO  `t_people`  VALUES ( 8,  ‘104‘,  ‘田七‘);
 INSERT  INTO  `t_people`  VALUES ( 9,  ‘100‘,  ‘嘻嘻‘);
 INSERT  INTO  `t_people`  VALUES ( 10,  ‘100‘,  ‘小粉丝‘);
                                                                       

3、查询 people_no 重复的记录

  SELECT * 
 FROM t_people 
 WHERE people_no  IN (
	 SELECT people_no  FROM t_people  GROUP  BY people_no  HAVING  COUNT(people_no) >  1
);
            

4、查询 people_no 重复且不包含 id 最小的记录

  SELECT * 
 FROM t_people 
 WHERE people_no  IN (
	 SELECT people_no  FROM t_people  GROUP  BY people_no  HAVING  COUNT(people_no) >  1
)  AND  id  NOT  IN (
	 SELECT  MIN( id)  FROM t_people  GROUP  BY people_no  HAVING  COUNT(people_no) >  1
);
                         

5、查询 people_no 和 people_name 重复的记录

  SELECT * 
 FROM t_people 
 WHERE (people_no, people_name)  IN (
	 SELECT people_no, people_name  FROM t_people  GROUP  BY people_no, people_name  HAVING  COUNT(*) >  1
);
            

6、查询 people_no 和 people_name 重复且不包含 id 最小的记录

  SELECT * 
 FROM t_people 
 WHERE (people_no, people_name)  IN (
	 SELECT people_no, people_name  FROM t_people  GROUP  BY people_no, people_name  HAVING  COUNT(*) >  1 
)  AND  id  NOT  IN (
	 SELECT  MIN( id)  FROM t_people  GROUP  BY people_no, people_name  HAVING  COUNT(*) >  1
);
                         

本文参考：https://www.cnblogs.com/LDDXFS/p/9867928.html

查询数据库中的重复数据——MySQL数据库

标签：参考   innodb   bsp   htm   post   str   utf8mb4   插入数据   数据   

查看更多关于查询数据库中的重复数据——MySQL数据库的详细内容...