如何删除最小值重复

Question

我有一个由以下列组成的表：

  id      name    value
serial    text    text

并且我需要移除具有最小id值和name = 'SUPPORT_EMAIL'的行。我怎样才能以最佳方式做到这一点？我试过了：

DELETE FROM propertie 
WHERE name = 'SUPPORT_EMAIL' 
HAVING id = MIN(id) 
GROUP BY name ;

和

DELETE FROM propertie 
WHERE name = 'SUPPORT_EMAIL' 
GROUP BY name 
HAVING id = MIN(id)  ;

但它没有起作用。当然，我可以写

DELETE FROM propertie 
WHERE id = (SELECT id 
        FROM propertie 
        WHERE name = 'SUPPORT_EMAIL' and id = (SELECT MIN(id) 
                            FROM propertie 
                            WHERE name = 'SUPPORT_EMAIL')
)

但对我来说似乎更糟。

Answer 1

您已经很接近了，但可以使用简化您的工作

DELETE FROM propertie 
WHERE id = (SELECT MIN(id) 
            FROM   propertie 
            WHERE  name = 'SUPPORT_EMAIL')

你也可以：

DELETE FROM propertie 
WHERE id = (SELECT id
            FROM   propertie 
            WHERE  name = 'SUPPORT_EMAIL'
            ORDER BY id
            LIMIT  1)

我不确定这是否是合法的PostgreSQL语法：

DELETE FROM   propertie 
WHERE  name = 'SUPPORT_EMAIL'
ORDER BY id
LIMIT  1

Answer 2

为了在删除查询中使用聚合函数，我们需要使用下面的子查询。我觉得这是优化的方式：

DELETE FROM attendence 
WHERE
  id IN 
     (select min(id) from attendence where name = 'SUPPORT_EMAIL' );

Answer 3

您的问题还不清楚，但如果有多个重复的行，并且您想删除除最后一个（id最高的行）之外的所有重复行，则可以使用以下查询：

DELETE FROM propertie p1
USING propertie p2
WHERE
  p1.name=p2.name
  AND p1.id<p2.id;

或者，如果您只想删除重复的SUPPORT_EMAIL:

DELETE FROM propertie p1
USING propertie p2
WHERE
  p1.name='SUPPORT_EMAIL'
  AND p1.name=p2.name
  AND p1.id<p2.id;

请看这里的小提琴示例。