sql - 如何删除最小值重复项？

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