查找重复记录的高效查询 [英] Efficient query for finding duplicate records
本文介绍了查找重复记录的高效查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要查询一个表中的重复存款记录,其中在某个时间窗口内在一个现金终端进行的两次相同金额的存款被视为重复记录.我现在已经开始处理一个查询,但如果您就正确"执行此操作提出任何建议或建议,我将不胜感激.
I need to query a table for duplicate deposit records, where two deposits made at one cash terminal, for the same amount, within a certain time window, are considered duplicate records. I've started working on a query now, but I would appreciate any advice or suggestions on doing this 'properly'.
推荐答案
通常,您会对同一个表进行自联接,并将重复"条件放在联接条件中.
Generally, you'd do a self join to the same table, and put your "duplicate" criteria in the join conditions.
例如
SELECT
*
FROM
Transactions t1
inner join
Transactions t2
on
t1.Terminal = t2.Terminal and
t1.Amount = t2.Amount and
DATEDIFF(minute,t2.TransactionDate,t1.TransactionDate) between 0 and 10 and
t1.TransactionID > t2.TransactionID /* prevent matching the same row */
这篇关于查找重复记录的高效查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文