Remove Duplicates in Postgresql

1
2
3
4
5
6
7
8
DELETE
FROM des_table
WHERE id IN (SELECT id
            FROM des_table
            WHERE dup_col IN (SELECT dup_col FROM des_table GROUP BY dup_col HAVING count(dup_col) > 1))
	AND id NOT IN (SELECT min(id)
                  FROM des_table
                  WHERE dup_col IN (SELECT dup_col FROM des_table GROUP BY dup_col HAVING count(dup_col) > 1));

效果就是保留了重复记录dup_colid最小的那个

想到个更简单的

1
2
3
DELETE
FROM dst_table
WHERE id NOT IN (SELECT max(id) FROM dst_table GROUP BY dup_col);
加载评论