/*
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-- ----------------------------DROPTABLEIFEXISTS`tb_user`;CREATETABLE`tb_user`(`id`intNOTNULLAUTO_INCREMENT,`name`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_general_ci NULLDEFAULTNULL,`age`intNULLDEFAULTNULL,`sex`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_general_ci NULLDEFAULTNULL,`score`decimal(10,2)NULLDEFAULTNULL,`rq`dateNULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=4CHARACTERSET= utf8mb4 COLLATE= utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of tb_user-- ----------------------------INSERTINTO`tb_user`VALUES(1,'张三',12,'0',52.00,'2024-08-01');INSERTINTO`tb_user`VALUES(2,'张三',12,'0',68.00,'2024-08-07');INSERTINTO`tb_user`VALUES(3,'张三',12,'0',62.00,'2024-08-17');INSERTINTO`tb_user`VALUES(4,'李四',12,'0',66.00,'2024-08-21');INSERTINTO`tb_user`VALUES(5,'王五',35,'1',10.00,'2024-08-21');SET FOREIGN_KEY_CHECKS =1;
二、根据全部字段的去重查询
SELECTDISTINCT*FROM tb_user;
三、根据某些字段的去重查询(不考虑查询其他字段)
SELECTDISTINCT name FROM tb_user;
SELECT name FROM tb_user GROUPBY name;
四、根据某些字段的去重查询(考虑查询其他字段)
SELECT name,GROUP_CONCAT(age) age FROM tb_user GROUPBY name;
五、根据某些字段的去重查询,查询重复项以外的全部数据,如果要查询最新数据则使用max
select*from tb_user where id in(selectmin(id) minid from tb_user groupby name)
六、根据某些字段的去重查询,查询重复项
select*from tb_user where id notin(selectmin(id) minid from tb_user groupby name)
七、删除重复数据
deletefrom tb_user where id in(select*from(select id from tb_user where id notin(selectmin(id)from tb_user groupby name)) a)