数据库去重，group by、distinct、rowid的用法，oracle和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;

查询结果集

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