mysql - 基于子查询的SQL查询。检索数据>阈值的交易

Question

我的数据库表称为事务，如下所示:

 Name |    Date (DateTime)   | Type |  Stock    | Volume | Price | Total
 Tom    2014-05-24 12:00:00    Sell   Barclays     100      2.2     220.0
 Bob    2014-04-13 15:00:00    Buy    Coca-Cola    10       12.0    120.0

varchar    DateTime           varchar varchar      int      float   float   

我最初的问题是从表中删除属于第一个事务晚于特定阈值的用户的所有事务。我的查询是:

DELETE FROM transactions WHERE name NOT IN (SELECT name FROM transactions2 WHERE date < CAST('2014-01-01 12:00:00.000' as DateTime));
Query OK, 35850 rows affected (3 hours 5 min 28.88 sec)

我认为这是一个糟糕的解决方案，我必须复制该表以避免从我正在读取的同一个表中删除，并且执行花费了相当长的时间(对于包含〜170k行的表来说需要3个小时)

现在我正在尝试删除属于其最新交易发生在特定阈值日期之前的用户的所有交易。

DELETE FROM transactions WHERE name IN (SELECT name FROM transactions HAVING max(date) < CAST('2015-01-01 12:00:00.000' as DateTime) );

遗憾的是，子查询只找到一个结果:

SELECT name FROM transactions HAVING max(date) < CAST('2015-01-01 12:00:00.000' as DateTime)';

+------------+
| name       |
+------------+
| david      |
+------------+

我想由于 max() 函数，我只得到一个结果。我不是 SQL 专家，但我很清楚我在集合和逻辑方面需要什么。我非常乐意就如何重写我的查询提出建议。

编辑:这是一个包含架构和一些数据的 sqlfiddle:http://sqlfiddle.com/#!2/389ede/2

我需要删除 alex 的所有条目，因为他的最后一笔交易发生在某个阈值之前(假设是 2013 年 1 月 1 日)。不需要删除 Tom 的交易，因为他的最新交易是在 2013 年 1 月 1 日之后进行的。

Answer 1

您的第一个查询可以表述为:“从之前不存在该用户交易的交易中删除用户？”。这很容易转换为sql:

delete from transactions t1
where not exists (
    select 1 from transactions t2
    where t1.name = t2.name
      and t2.date < ?
)

mysql 仍然不支持(据我所知)从 select 中引用的表中删除，因此我们需要将其重写为:

delete t1.* 
from transactions t1
left join transactions t2
    on t1.name = t2.name
   and t2.date < ?
where t2.name is null

日期是保留字，因此您必须引用它。

您的第二个查询可以用相同的方式解决，从不存在的交易中删除特定日期之后的交易。我将把它作为练习。