在清洗数据的时候,经常会遇到去重的需求,特地在这里记录一下。

查询重复的数据

select * , row_number() over (partition by 字段 order by  字段) as rowid from 表名
where  字段 in (select  字段 from 表名
group by  字段 having count(字段)>1)

查询MySQL 也适用

删除重复的数据,保留一条

delete t from (select * , row_number() over (partition by 字段 order by  字段) as rowid from 表名
where  字段 in (select  字段 from 表名
group by  字段 having count(字段)>1)) t where t.rowid != 1

MySql删除重复记录

delete from 表名 where Id in (select t.Id from (
select * , row_number() over (partition by 字段 order by  字段) as rowid from 表名
where  字段 in (select  字段 from 表名
group by  字段 having count(字段)>1)) t where t.rowid!=1)