删除表中重复的记录：使用窗口函数 row_number() over 或 选择复制insert into select 或普通的子表分组select max group by having

1. 最好用的 窗口函数

用ROW_NUMBER删除重复数据

假设表TAB中有a,b,c三列，可以使用下列语句删除a,b,c都相同的重复行。

DELETE FROM 
(
    select year,QUARTER,RESULTS,
    -- 分组和排序 和查出的值，都是 查出这3个字段。如果row_no不是 第一个就删除
    row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO 
    FROM SALE )

WHERE ROW_NO>1

2. 不保留

1. 只要有重复，一条记录也不留

CREATE TABLE `stu` (
  `id` int(11) NOT NULL,
  `name` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DELETE FROM stu WHERE name IN (
	
    -- 注意：一定得用一个 中间表
	SELECT t1.name FROM (
		-- 查询出所有重复的user_name
		SELECT name FROM stu GROUP BY name HAVING COUNT(1)>1
	) t1
	
)


delete from 表 where 重复字段 in 
(
    select 重复字段 from 表 group by 重复字段 having count(*)>1
)

3. 保留一条记录

1. 使用 中间表。表数据都相同

-- 创建临时表
create table my_tmp like stu

-- 插入临时表里数据，去重后。 （没有ID的情况）	
insert into my_tmp
select distinct * from stu
-- 选择插入。如果查询的是自己，叫：自我复制

-- 删除原表
drop table stu;

-- 重命名 tmp 表为 stu
rename table my_tmp to stu

-- 删除 临时表
drop table my_tmp

2. 表数据有ID

查询出这条冗余的数据

select * from 表 where 重复字段 in 
(
 	select 重复字段 from 表 group by 重复字段 having count(*)>1
)

-- 方式1
select * from stu where name in 
(
 -- 查询出 冗余的 name
 select name from stu group by name having count(1)>1
)

-- 并且： id 不为 最小的ID
and id not in 
(
  -- 查询重复的 name 字段， 取最小的ID
  select min(id) name from stu group by name having count(1)>1
)


-- 方式2：
-- id 不在 这里的 
select * from stu where id not in (
    -- 查询出 最小的ID，根据name 分组，如果name 多个
    -- 只会返回一个
	select min(id) from stu group by name
)

进行删除

delete from stu where id not in ( -- 删除ID，不在这里面
	-- 包一层中间表
	select t.id from (
			select min(id) id from stu group by name --根据name分组，取出最小的 id
		) t --起别名为 t

)

表中没有ID

查询全部 重复的记录

-- 和上面2 一样

select * from stu where name in 
(
	select name from stu group by name having count(*) > 1
)

2、过滤重复记录(只显示一条)

select * from stu where id in 
(
	select max(id) from stu group by name having count(1) > 1
)