当昨天的数据与今天的数据匹配时，MySQL 从表中删除指定的行

Question

我有一些数据存储在一个名为 MyTable 的 MySQL 表中，如下所示:

+----------+-----------+---------------------+
|    my_id |    amount | updated             |
+----------+-----------+---------------------+
|   105415 |        81 | 2013-02-13 00:00:00 |
|   105414 |        33 | 2013-02-13 00:00:00 |
|   220801 |       240 | 2013-02-13 00:00:00 |
|   105411 |       118 | 2013-02-13 00:00:00 |
|   105411 |       118 | 2013-02-12 00:00:00 |
|   220801 |       240 | 2013-02-12 00:00:00 |
|   105414 |        33 | 2013-02-11 00:00:00 |
|   105415 |        81 | 2013-02-11 00:00:00 |
+----------+-----------+---------------------+

我想做的是删除昨天 (2013-02-12) 与今天 (2013-02-13) 具有相同 my_id 和金额的所有行。

我知道如何选择今天的所有数据:

SELECT * FROM 'MyTable' WHERE 'updated' = CURDATE()

我知道如何选择昨天的所有数据:

SELECT * FROM 'MyTable' WHERE 'updated' = CURDATE()-1

我知道 DELETE 命令的语法(DELETE FROM 'MyTable' WHERE...)。

如何删除昨天和今天的 my_id 和金额匹配的行？

在给出的示例中，我想删除这些行:

|   105411 |       118 | 2013-02-12 00:00:00 |
|   220801 |       240 | 2013-02-12 00:00:00 |

我想保留这些行:

+----------+-----------+---------------------+
|    my_id |    amount | updated             |
+----------+-----------+---------------------+
|   105415 |        81 | 2013-02-13 00:00:00 |
|   105414 |        33 | 2013-02-13 00:00:00 |
|   220801 |       240 | 2013-02-13 00:00:00 |
|   105411 |       118 | 2013-02-13 00:00:00 |


|   105414 |        33 | 2013-02-11 00:00:00 |
|   105415 |        81 | 2013-02-11 00:00:00 |
+----------+-----------+---------------------+

Answer 1

对@sgeddes 的回答进行了一些修改，我认为这对您的回答更准确一些:

无论如何，请将@sgeddes 的回答作为正确答案归功于他的回答:

DELETE t1 FROM MyTable AS t1, MyTable as t2
WHERE t1.updated = CURDATE()-1
  AND t2.updated =  CURDATE()
  AND t2.my_id = t1.my_id
  And t2.amount = t1.amount;

如果你有主键，你也可以使用这个(这是我更喜欢的SQL)

DELETE FROM MyTable 
WHERE  updated = CURDATE()-1 
  AND (my_id,amount) in (select my_id,amount 
                         from MyTable
                         where updated = CURDATE());