MySQL: 去重查询

一、数据库表创建

/*Navicat Premium Data TransferSource Server         : localhostSource Server Type    : MySQLSource Server Version : 80032Source Host           : localhost:3306Source Schema         : mt-databaseTarget Server Type    : MySQLTarget Server Version : 80032File Encoding         : 65001Date: 22/08/2024 09:30:49
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,`age` int NULL DEFAULT NULL,`sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,`score` decimal(10, 2) NULL DEFAULT NULL,`rq` date NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES (1, '张三', 12, '0', 52.00, '2024-08-01');
INSERT INTO `tb_user` VALUES (2, '张三', 12, '0', 68.00, '2024-08-07');
INSERT INTO `tb_user` VALUES (3, '张三', 12, '0', 62.00, '2024-08-17');
INSERT INTO `tb_user` VALUES (4, '李四', 12, '0', 66.00, '2024-08-21');
INSERT INTO `tb_user` VALUES (5, '王五', 35, '1', 10.00, '2024-08-21');SET FOREIGN_KEY_CHECKS = 1;

二、根据全部字段的去重查询

SELECT DISTINCT * FROM tb_user;

三、根据某些字段的去重查询（不考虑查询其他字段）

SELECT DISTINCT name FROM tb_user;

SELECT name FROM tb_user GROUP BY name;

四、根据某些字段的去重查询（考虑查询其他字段）

SELECT name,GROUP_CONCAT(age) age FROM tb_user GROUP BY name;

五、根据某些字段的去重查询，查询重复项以外的全部数据，如果要查询最新数据则使用max

select * from tb_user where id in (select min(id) minid from tb_user group by name)

六、根据某些字段的去重查询，查询重复项

select * from tb_user where id not in (select min(id) minid from tb_user group by name)

七、删除重复数据

delete from tb_user where id in (select * from (select id from tb_user where id not in (select min(id) from tb_user group by name)) a)