查找重复值 [英] Finding duplicate values

问题描述

我有一个名为Transfers的表，我想在Doc ID，Amount和Date三列中查找具有重复值的所有记录. 基本上我需要找到Doc id，金额和日期相同的位置 可以用来找到这些重复项的最佳查询是什么?

I have a table called Transfers, and I would like to find all the records that have duplicate values on three columns Doc ID,Amount and Date. Basically what I need is to find where Doc id ,amount and dates are the same What is the best query I can use to find these duplicates?

我尝试了以下查询

select transfers.doc_id,transfers.date,transfers.amount,
  from transfers 
 where transfers.date between $P{StartDate} and $P{EndDate}
 group by doc_id
having doc_id >1;

这是寻找结果的地方:

Doc_id  Date        amount
1234    12/07/2019  3,000
1234    12/07/2019  3,000
2345    12/07/2019  15,000
2345    12/07/2019  15,000
4321    12/07/2019  5,600
4321    12/07/2019  5,600

推荐答案

考虑对三列使用group by group by doc_id,Amount,Date

select t.doc_id,t.date,t.amount
  from transfers t
 where t.date between $P{StartDate} and $P{EndDate}
 group by doc_id,Amount,Date
having count(doc_id) >1;

这篇关于查找重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！