查询数据库中的重复数据——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   插入数据   数据