在两个字段中删除重复的行 [英] Delete rows with duplicates on two fields

问题描述

我需要删除此表的重复值：

  + ---- + ------- + ------------- + --------- + 
 | id |名称|描述|姓| 
 + ---- + ------- + ------------- + --------- + 
 | 1 |彼得|成员| Hitsh | 
 | 2 |詹姆斯|成员| Tach | 
 | 3 |玛丽|成员| Popims | 
 | 4 |彼得|成员| Hitsh | 
 + ---- + ------- + ------------- + --------- + 
  

我想删除所有重复的值与相同的名称和 surname 。

解决方案

> ： $

$

$

$

  DELETE FROM tbl 
 USING（
 SELECT id，row_number OVER（PARTITION BY name，surname ORDER BY id）As rn 
 FROM tbl 
）del 
 WHERE tbl.id = del.id 
 AND del.rn> 1; 
  

假设 id p>

今天非常相似的问题：

使用聚合和子查询别名在Postgres中删除行

I need to delete duplicated values of this table:

+----+-------+-------------+---------+
| id | name  | description | surname |
+----+-------+-------------+---------+
| 1  | Peter | Member      | Hitsh   |
| 2  | James | Member      | Tach    |
| 3  | Mary  | Member      | Popims  |
| 4  | Peter | Member      | Hitsh   |
+----+-------+-------------+---------+

I would want to remove all the duplicated values with the same name and surname.

解决方案

To keep the row with the smallest id for every set of duplicates on (name, surname):

DELETE FROM tbl
USING (
   SELECT id, row_number OVER (PARTITION BY name, surname ORDER BY id) As rn
   FROM   tbl
   ) del
WHERE  tbl.id = del.id
AND    del.rn > 1;

Assuming id to be unique.

Very similar question today:
Deleting rows in postgres using aggregates and subquery aliases

这篇关于在两个字段中删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！