我有一个交易表：
Transactions------------ID | account | type | date_time | amount---------------------------------------------------- 1 | 001 | 'R' | '2012-01-01 10:01:00' | 1000 2 | 003 | 'R' | '2012-01-02 12:53:10' | 1500 3 | 003 | 'A' | '2012-01-03 13:10:01' | -1500 4 | 002 | 'R' | '2012-01-03 17:56:00' | 2000 5 | 001 | 'R' | '2012-01-04 12:30:01' | 1000 6 | 002 | 'A' | '2012-01-04 13:23:01' | -2000 7 | 003 | 'R' | '2012-01-04 15:13:10' | 3000 8 | 003 | 'R' | '2012-01-05 12:12:00' | 1250 9 | 003 | 'A' | '2012-01-06 17:24:01' | -1250并且我希望选择所有特定类型(‘R’),但不是那些立即(按照date_time字段的顺序)为同一帐户提交的另一种类型(‘A’)的另一个交易…
因此,在前面的示例中,查询应抛出以下行：
ID | account |type | date | amount---------------------------------------------------- 1 | 001 | 'R' | '2012-01-01 10:01:00' | 1000 5 | 001 | 'R' | '2012-01-04 12:30:01' | 1000 7 | 003 | 'R' | '2012-01-04 15:13:10' | 3000(如您所见,第2行未显示,因为第3行’取消’它…第4行’第4行被’取消’;行7确实出现(即使帐户003属于已取消的第2行),这次在第7行,它没有被任何’A’行取消;并且第8行也不会出现(它也是003帐户,因为现在这个被9取消,这也不会取消7,只是前一个一：8 ……
我在Where子句中尝试了Joins,子查询,但我真的不确定如何进行查询…
我尝试过的：
尝试加入：
SELECT trans.type as type,trans.amount as amount,trans.date_time as dt,trans.account as acct,FROM Transactions transINNER JOIN ( SELECT t.type AS type,t.acct AS acct,t.date_time AS date_time FROM Transactions t WHERE t.date_time > trans.date_time ORDER BY t.date_time DESC ) AS nextTrans ON nextTrans.acct = trans.acct WHERE trans.type IN ('R') AND nextTrans.type NOT IN ('A') ORDER BY DATE(trans.date_time) ASC这会引发错误,因为我无法将外部值引入MysqL中的JOIN.
在以下位置尝试子查询：
SELECT trans.type as type,FROM Transactions trans WHERE trans.type IN ('R') AND trans.datetime < ( SELECT t.date_time AS date_time FROM Transactions t WHERE t.account = trans.account ORDER BY t.date_time DESC ) AS nextTrans ON nextTrans.acct = trans.acct ORDER BY DATE(trans.date_time) ASC这是错误的,我可以将外部值引入MysqL中的WHERE,但我无法找到正确过滤我需要的方法…
重要编辑：
我设法实现了解决方案,但现在需要认真优化.这里是：
SELECT * FROM (SELECT t1.*,tFlagged.ID AS cancID,tFlagged.type AS cancFlag FROM transactions t1 left JOIN (SELECT t2.* FROM transactions t2 ORDER BY t2.date_time ASC ) tFlagged ON (t1.account=tFlagged.account AND t1.date_time < tFlagged.date_time) WHERE t1.type = 'R' GROUP BY t1.ID) tCanc WHERE tCanc.cancFlag IS NulL OR tCanc.cancFlag <> 'A'我自己加入了这个表,只考虑了相同的帐户和很棒的date_time. Join按date_time排序.按ID分组我设法只获得了连接的第一个结果,这恰好是同一帐户的下一个事务.
然后在外部选择上,我过滤掉那些具有“A”的东西,因为这意味着下一个交易实际上是对它的取消.换句话说,如果同一个帐户没有下一个交易,或者下一个交易是’R’,那么它不会被取消,并且必须在结果中显示…
我懂了：
+----+---------+------+---------------------+--------+--------+----------+| ID | account | type | date_time | amount | cancID | cancFlag |+----+---------+------+---------------------+--------+--------+----------+| 1 | 001 | R | 2012-01-01 10:01:00 | 1000 | 5 | R || 5 | 001 | R | 2012-01-04 12:30:01 | 1000 | NulL | NulL || 7 | 003 | R | 2012-01-04 15:13:10 | 3000 | 8 | R |+----+---------+------+---------------------+--------+--------+----------+它将每个交易与下一个交易关联到同一个帐户,然后筛选出已取消的交易…成功!!
正如我所说,现在的问题是优化.我的真实数据有很多行(因为预计会有时间跨越事务的表),而对于现在约有10,000行的表,我在1分44秒内得到了一个积极的结果.我想这就是加入的东西……(对于那些在这里知道协议的人,我该怎么做？在这里发一个新问题并将其作为解决方案发布到这个？或者只是在这里等待更多答案？)
最佳答案这是一个基于嵌套子查询的解决方案.首先,我添加了几行来捕获更多案例.例如,交易10不应该取消交易10,因为交易11介于两者之间.> select * from transactions order by date_time;+----+---------+------+---------------------+--------+| ID | account | type | date_time | amount |+----+---------+------+---------------------+--------+| 1 | 1 | R | 2012-01-01 10:01:00 | 1000 || 2 | 3 | R | 2012-01-02 12:53:10 | 1500 || 3 | 3 | A | 2012-01-03 13:10:01 | -1500 || 4 | 2 | R | 2012-01-03 17:56:00 | 2000 || 5 | 1 | R | 2012-01-04 12:30:01 | 1000 || 6 | 2 | A | 2012-01-04 13:23:01 | -2000 || 7 | 3 | R | 2012-01-04 15:13:10 | 3000 || 8 | 3 | R | 2012-01-05 12:12:00 | 1250 || 9 | 3 | A | 2012-01-06 17:24:01 | -1250 || 10 | 3 | R | 2012-01-07 00:00:00 | 1250 || 11 | 3 | R | 2012-01-07 05:00:00 | 4000 || 12 | 3 | A | 2012-01-08 00:00:00 | -1250 || 14 | 2 | R | 2012-01-09 00:00:00 | 2000 || 13 | 3 | A | 2012-01-10 00:00:00 | -1500 || 15 | 2 | A | 2012-01-11 04:00:00 | -2000 || 16 | 2 | R | 2012-01-12 00:00:00 | 5000 |+----+---------+------+---------------------+--------+16 rows in set (0.00 sec)首先,创建一个查询,为每个事务“获取同一帐户中该事务之前的最近事务的日期”：
SELECT t2.*,MAX(t1.date_time) AS prev_dateFROM transactions t1JOIN transactions t2ON (t1.account = t2.account AND t2.date_time > t1.date_time)GROUP BY t2.account,t2.date_timeORDER BY t2.date_time;+----+---------+------+---------------------+--------+---------------------+| ID | account | type | date_time | amount | prev_date |+----+---------+------+---------------------+--------+---------------------+| 3 | 3 | A | 2012-01-03 13:10:01 | -1500 | 2012-01-02 12:53:10 || 5 | 1 | R | 2012-01-04 12:30:01 | 1000 | 2012-01-01 10:01:00 || 6 | 2 | A | 2012-01-04 13:23:01 | -2000 | 2012-01-03 17:56:00 || 7 | 3 | R | 2012-01-04 15:13:10 | 3000 | 2012-01-03 13:10:01 || 8 | 3 | R | 2012-01-05 12:12:00 | 1250 | 2012-01-04 15:13:10 || 9 | 3 | A | 2012-01-06 17:24:01 | -1250 | 2012-01-05 12:12:00 || 10 | 3 | R | 2012-01-07 00:00:00 | 1250 | 2012-01-06 17:24:01 || 11 | 3 | R | 2012-01-07 05:00:00 | 4000 | 2012-01-07 00:00:00 || 12 | 3 | A | 2012-01-08 00:00:00 | -1250 | 2012-01-07 05:00:00 || 14 | 2 | R | 2012-01-09 00:00:00 | 2000 | 2012-01-04 13:23:01 || 13 | 3 | A | 2012-01-10 00:00:00 | -1500 | 2012-01-08 00:00:00 || 15 | 2 | A | 2012-01-11 04:00:00 | -2000 | 2012-01-09 00:00:00 || 16 | 2 | R | 2012-01-12 00:00:00 | 5000 | 2012-01-11 04:00:00 |+----+---------+------+---------------------+--------+---------------------+13 rows in set (0.00 sec)将其用作子查询以使每个事务及其前任在同一行上.使用一些过滤来抽出我们感兴趣的交易 – 即’A’交易,其前身是’R’交易,它们完全取消 –
SELECT t3.*,transactions.*FROM transactions JOIN (SELECT t2.*,MAX(t1.date_time) AS prev_date FROM transactions t1 JOIN transactions t2 ON (t1.account = t2.account AND t2.date_time > t1.date_time) GROUP BY t2.account,t2.date_time) t3 ON t3.account = transactions.account AND t3.prev_date = transactions.date_time AND t3.type='A' AND transactions.type='R' AND t3.amount + transactions.amount = 0 ORDER BY t3.date_time;+----+---------+------+---------------------+--------+---------------------+----+---------+------+---------------------+--------+| ID | account | type | date_time | amount | prev_date | ID | account | type | date_time | amount |+----+---------+------+---------------------+--------+---------------------+----+---------+------+---------------------+--------+| 3 | 3 | A | 2012-01-03 13:10:01 | -1500 | 2012-01-02 12:53:10 | 2 | 3 | R | 2012-01-02 12:53:10 | 1500 || 6 | 2 | A | 2012-01-04 13:23:01 | -2000 | 2012-01-03 17:56:00 | 4 | 2 | R | 2012-01-03 17:56:00 | 2000 || 9 | 3 | A | 2012-01-06 17:24:01 | -1250 | 2012-01-05 12:12:00 | 8 | 3 | R | 2012-01-05 12:12:00 | 1250 || 15 | 2 | A | 2012-01-11 04:00:00 | -2000 | 2012-01-09 00:00:00 | 14 | 2 | R | 2012-01-09 00:00:00 | 2000 |+----+---------+------+---------------------+--------+---------------------+----+---------+------+---------------------+--------+4 rows in set (0.00 sec)从上面的结果可以看出我们几乎就在那里 – 我们已经确定了不需要的交易.使用left JOIN,我们可以从整个事务集中筛选出这些：
SELECT transactions.*FROM transactionsleft JOIN (SELECT transactions.ID FROM transactions JOIN (SELECT t2.*,MAX(t1.date_time) AS prev_date FROM transactions t1 JOIN transactions t2 ON (t1.account = t2.account AND t2.date_time > t1.date_time) GROUP BY t2.account,t2.date_time) t3 ON t3.account = transactions.account AND t3.prev_date = transactions.date_time AND t3.type='A' AND transactions.type='R' AND t3.amount + transactions.amount = 0) t4 USING(ID) WHERE t4.ID IS NulL AND transactions.type = 'R' ORDER BY transactions.date_time;+----+---------+------+---------------------+--------+| ID | account | type | date_time | amount |+----+---------+------+---------------------+--------+| 1 | 1 | R | 2012-01-01 10:01:00 | 1000 || 5 | 1 | R | 2012-01-04 12:30:01 | 1000 || 7 | 3 | R | 2012-01-04 15:13:10 | 3000 || 10 | 3 | R | 2012-01-07 00:00:00 | 1250 || 11 | 3 | R | 2012-01-07 05:00:00 | 4000 || 16 | 2 | R | 2012-01-12 00:00:00 | 5000 |+----+---------+------+---------------------+--------+以上是内存溢出为你收集整理的mysql – SQL：选择行不在同一个表中的条件的事务全部内容，希望文章能够帮你解决mysql – SQL：选择行不在同一个表中的条件的事务所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）