MySQL: 去重查询
一、数据库表创建/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80032
Source Host : localhost:3306
Source Schema : mt-database
Target Server Type : MySQL
Target Server Version : 80032
File Encoding : 65001
Date: 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)
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]