文章目录

  • 数据库去重
  • 一、Group by
  • 1.1、概述
  • 1.2、用法
  • 1.3、演示
  • 1.3.1、建表
  • 1.3.2、查询
  • 二、distinct
  • 1.1、概述
  • 1.2、用法
  • 1.3、演示
  • 三、rowid方法
  • 四、删除重复数据保留一条
  • 4.1、oracle
  • 4.2、mysql


数据库去重

一、Group by

1.1、概述

  • group_by的意思是根据by对数据按照哪个字段进行分组，或者是哪几个字段进行分组。

1.2、用法

select  字段   from  表名  where   条件   group  by    字段
或者
select  字段   from  表名  group  by   字段   having   过滤条件

1.3、演示

1.3.1、建表
-- Table structure for student

-- ----------------------------

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(0) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `score` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES (1, '张三', '语文', '80');
INSERT INTO `student` VALUES (2, '李四', '数学', '90');
INSERT INTO `student` VALUES (1, '张三', '数学', '40');
INSERT INTO `student` VALUES (4, '赵五', '数学', '60');
INSERT INTO `student` VALUES (3, '老六', '英语', '20');
INSERT INTO `student` VALUES (1, '小七', '政治', '90');

SET FOREIGN_KEY_CHECKS = 1;

查询结果集

mysql group by 去重后相加 数据库去重group by_ci

1.3.2、查询

1、根据一个字段分组

  • 按学科分组（按名字分组也是一样）
select subject from student GROUP BY subject

2、多个字段（看成一个整体）

  • 按照姓名和学科分组
select name,subject from student GROUP BY name,subject

3、常用的聚合函数：count() , sum() , avg() , max() , min()

//查询重复的数量
select name,count(1) from student GROUP BY name;
//查询和
select name,sum(score) from student group by name ;

4、having 与 where

  • where 在分组之前过滤数据，将不符合条件的筛选掉，where条件中不能包含聚组函数
  • having 分组之后过滤数据，条件中经常包含聚组函数
  • 可以一起使用
//查询重复大于一次的
select name,count(1) as count from student GROUP BY name HAVING count >1
//一起使用
select name,count(1) as count from student where name = '张三' GROUP BY name HAVING count >1

二、distinct

1.1、概述

  • distinct用来查询不重复记录的条数，distinct只能返回他的目标字段，而无法返回其他字段。

1.2、用法

select  distinct 字段   from  表名
1.3、演示

1、一个字段

select distinct name from student

2、两个字段

select distinct name,subject from student

3、与函数一起使用

select count(distinct name) from student

三、rowid方法

1.1、概述

  • Oracle带的rowid属性，可以进行判断是否存在重复语句；

1.2、用法

//一张表
Select * from 表1 a Where rowid !=(select max(rowid) from 表1 b Where 字段关联)
//两张表
Select * from 表1 a Where rowid !=(select max(rowid) from 表2 b Where 字段关联)

四、删除重复数据保留一条

4.1、oracle

  • Oracle带的rowid属性，可以获取到rowid最大或者最小的一条

1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断

//先查询重复的id，再用in查询到重复的数据
select * from 表 where Id in (select Id from 表 group by Id having count(Id) > 1)

2、删除表中多余的重复记录，只留有rowid最小的记录（最大max,最小min）

DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) 
AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

3、查找表中多余的重复记录（多个字段）

select * from 表 a where (a.Id,a.name) in(select Id,name from 表 group by Id,name having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from 表 a where (a.Id,a.name) in (select Id,name from 表 group by Id,name having count(*) > 1)
and rowid not in (select min(rowid) from 表 group by Id,name having count(*)>1)

4.2、mysql

  • mysql 不存在rowid ，可以使用主键代替

1、查询重复数据

select * from 表 a where (a.Id,a.name) in(select Id,name from 表 group by Id,name having count(*) > 1)

2、删除表中多余的重复记录，保留主键最小的

select * from 表 a where (a.Id,a.name) in(select Id,name from 表 group by Id,name having count(*) > 1)
and id not in (select min(id) from 表 group by Id,name having count(*)>1)
  • 如果没有主键（用临时表去重），数据量大的表不推荐
//创建临时表
create table new_table_temp
//数据去重
select DISTINCT 字段1,字段2,字段3,字段4,字段5,字段6 from table_name
//删除原表
delete from table_name
//插入原表
insert into table_name
select * from new_table_temp
//删除临时表
DROP TABLE new_table_temp