mysql - 从单个 SQL 表中删除除最新条目以外的所有条目

Question

我有一个 SQL 表，其中包含每个 customerID 的多个条目(一些 customerID 只有一个我想保留的条目)。我需要使用 invoiceDate 字段作为我的标记，删除除每个 customerID 的最新条目之外的所有条目。

所以我需要从这里开始:

+------------+-------------+-----------+
| customerID | invoiceDate | invoiceID |
+------------+-------------+-----------+
|          1 |  1393995600 |       xx  |
|          1 |  1373688000 |       xx  |
|          1 |  1365220800 |       xx  |
|          2 |  1265220800 |       xx  |
|          2 |  1173688000 |       xx  |
|          3 |  1325330800 |       xx  |
+------------+-------------+-----------+

对此:

+------------+-------------+-----------+
| customerID | invoiceDate | invoiceID |
+------------+-------------+-----------+
|          1 |  1393995600 |       xx  |
|          2 |  1265220800 |       xx  |
|          3 |  1325330800 |       xx  |
+------------+-------------+-----------+

任何指导将不胜感激!

Answer 1

1. 编写查询以选择要删除的所有行:

SELECT * FROM t
WHERE invoiceDate NOT IN (
    SELECT MAX(invoiceDate)
    -- "FROM t AS t2" isn't supported by MySQL, see http://stackoverflow.com/a/14302701/227576
    FROM (SELECT * FROM t) AS t2
    WHERE t2.customerId = t.customerId
    GROUP BY t2.customerId
)

这在大型数据库上可能需要很长时间。

1. 如果您满意，请将查询更改为 DELETE 语句:

DELETE FROM t
WHERE invoiceDate NOT IN (
    SELECT MAX(invoiceDate)
    -- "FROM t AS t2" isn't supported by MySQL, see http://stackoverflow.com/a/14302701/227576
    FROM (SELECT * FROM t) AS t2
    WHERE t2.customerId = t.customerId
    GROUP BY t2.customerId
)

参见 http://sqlfiddle.com/#!9/6e031/1

如果您有多个行，其日期是同一客户的最新日期，您将不得不查找重复项并自行决定要保留哪一个。例如，查看上面 SQL fiddle 链接上的 customerId 2。