SQL查询基于子查询。检索具有数据的事务>阈 [英] SQL query based on subquery. Retrieve transactions with data > threshold

问题描述

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

My db table is called transactions and is like this:

 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   

我的初始问题是从表中删除所有的交易属于第一个事务晚于某个阈值的用户。
我的查询是：

My initial problem was to remove from the table ALL the transactions that belong to a user whose first transaction is later than a certain threshold. My query was:

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小时）

I think this is a poor solution, I had to duplicate the table to avoid deleting from the same table from where I am reading, and the execution took quite a long time (3 hours for a table containing ~170k rows)

现在我试图删除属于最新事务发生在一定阈值日期之前的用户的所有事务。

Now I am trying to delete ALL the transactions that belong to a user whose latest transaction happened before a certain threshold date.

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

可惜的是，子查询只能找到一个结果：

Sadly, the subquery finds only one result:

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

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

我想我由于max（）函数，我只得到一个结果。
我不是SQL的专家，但我非常了解我需要的集合和逻辑。
我真的很高兴有如何重写我的查询的建议。

I guess I am getting only one result because of the max() function. I am not an expert of SQL but I understand quite well what I need in terms of sets and logic. I would be really happy to have suggestions on how to rewrite my query.

编辑：
这是一个sqlfiddle与模式和一些数据： http://sqlfiddle.com/#!2/389ede/2

我需要删除所有的alex的条目，因为他最近的交易发生在某个阈值之前（比如说2013年1月1日）。
不需要删除tom的交易，因为他的最新时间是2013年1月1日。

I need to remove ALL the entries for alex, because his last transactions happened before a certain threshold (let's say 1 Jan 2013). Don't need to delete tom's transactions because he has his latest later than 1 Jan 2013.

推荐答案

查询可以表达为：从用户那里不存在该用户的事务的事务中删除用户？这很容易转换为sql：

Your first query can be formulated as: `delete users from transactions where it does not exist a transaction for that user before ?. This is easy to transform to sql:

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

mysql仍然不支持（AFAIK）从select中引用的表中删除，因此我们需要将其重写为：

mysql still does not support (AFAIK) deleting from a table that is referenced in a select, so we need to rewrite it as:

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

date是一个保留字，因此您必须引用

date is a reserved word so you will have to quote that.

您的第二个查询可以以相同的方式解决，从某个日期之后的事务中不存在的事务中删除。我会把它作为一个练习。

Your second query can be solved the same way, delete from transaction where it does not exists a transaction after a certain date. I'll leave it as an exercise.

这篇关于SQL查询基于子查询。检索具有数据的事务>阈的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！