postgresql - 根据列删除重复行

Question

我有一个名为 Aircraft 的表，其中有很多记录。问题是有些是重复的。我知道如何选择重复项及其计数:

SELECT flight_id, latitude, longitude, altitude, call_sign, measurement_time, COUNT(*)
FROM Aircraft
GROUP BY flight_id, latitude, longitude, altitude, call_sign, measurement_time
HAVING COUNT(*) > 1;

这会返回如下内容:

现在，我需要做的是删除重复项，每个只保留一个，这样当我再次运行查询时，所有计数都变为 1。

我知道我可以使用 DELETE 关键字，但我不确定如何从 SELECT 中删除它。

我确定我错过了一个简单的步骤，但我不想毁了我的数据库作为新手。

我该怎么做？

Answer 1

SELECT
    flight_id, latitude, longitude, altitude, call_sign, measurement_time
FROM Aircraft a
WHERE EXISTS (
    SELECT * FROM Aircraft x
    WHERE x.flight_id = a.flight_id
    AND x.latitude = a.latitude 
    AND x.longitude = a.longitude
    AND x.altitude = a.altitude
    AND x.call_sign  = a.call_sign
    AND x.measurement_time = a.measurement_time 
    AND x.id < a.id
 )
;

---

如果上面的查询返回正确的行(将被删除)您可以将其更改为删除语句:

---

DELETE
FROM Aircraft a
WHERE EXISTS (
    SELECT * FROM Aircraft x
    WHERE x.flight_id = a.flight_id
    AND x.latitude = a.latitude 
    AND x.longitude = a.longitude
    AND x.altitude = a.altitude
    AND x.call_sign  = a.call_sign
    AND x.measurement_time = a.measurement_time 
    AND x.id < a.id
 )
;