postgresql delete duplicated rows

Deleting duplicates

使用window function row_number 对数据partition

举例：

isnp=# create table student (id serial,name text,age int);
isnp=# d student;
isnp=# with cte as (select *,round(random()*100) as d from generate_series(1,10000) as r) insert into student (name,age)  select 'lmy'||r,d from cte;
### genetate duplicate row
isnp=# insert into student (name,age) select name,age from student where 100<id and id<200; 
isnp=# with xxx (id,name,age,rn)  as (select *,row_number() over(partition by name order by id) rn from student) delete from student where id in (select id from xxx where rn > 1);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!