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

I have some data stored in a MySQL table called MyTable like this:

我有一些数据存储在MySQL表MyTable中，像这样:

+----------+-----------+---------------------+
|    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 |
+----------+-----------+---------------------+

What I would like to do is delete all the rows from yesterday (2013-02-12) that have the same my_id and amount as today (2013-02-13).

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

I know how to select all the data from today:

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

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

从'MyTable'中选择*，其中' updates ' = CURDATE()

I know how to select all the data from yesterday:

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

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

从'MyTable'中选择*，其中' updates ' = CURDATE()-1

I know the syntax for the DELETE command (DELETE FROM 'MyTable' WHERE...).

我知道DELETE命令的语法(从“MyTable”中删除……)。

How do I delete the rows where the my_id and amount match for yesterday and today?

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

In the example given, I would want to delete these rows:

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

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

And I would want to keep these rows:

我想保留这些行

+----------+-----------+---------------------+
|    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 |
+----------+-----------+---------------------+

2 个解决方案

#1

Something like this should work:

像这样的东西应该是有用的:

DELETE M
FROM MyTable M
    INNER JOIN MyTable M2 ON
        M.My_Id = M2.My_Id AND  
        M.amount = M2.amount AND  
        M2.Updated = DATE_ADD(M.Updated, INTERVAL 1 DAY)

Here is the updated Fiddle with your sample data above: http://sqlfiddle.com/#!2/5e288/1

以下是您上面示例数据的更新版本:http://sqlfiddle.com/#!2/5e288/1

And the Results after the Delete:

以及删除后的结果:

MY_ID   AMOUNT  UPDATED
105415  81      February, 13 2013 00:00:00+0000
105414  33      February, 13 2013 00:00:00+0000
220801  240     February, 13 2013 00:00:00+0000
105411  118     February, 13 2013 00:00:00+0000
105414  33      February, 11 2013 00:00:00+0000
105415  81      February, 11 2013 00:00:00+0000

Good luck.

祝你好运。

#2

A bit modify from @sgeddes 's answer, I think this a bit more precise to your answer:

稍微修改一下@sgeddes的答案，我认为这比您的答案更准确一些:

Anyways, please give credit as correct answer to @sgeddes 's answer since his answer guided to this:

无论如何，请相信@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;

If you have a primary key, you can also use this (which is my prefer SQL)

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

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

#1