sql - Find duplicate values in SQL search

Question

I'm having a problem finding the right solution for this problem. I want to find duplicate invoices in a list of thousands of invoices and want to make sure were not paying the same invoice twice where the same invoice has been scanned in to our system twice but where the invoice number is not the same but has the same supplier id. I have tried to use COUNT to find all the invoices that have the same supplier id and the same amount but cannot get it to work.

(In the example I want to find the two Johns Bakery invoices)

标签： sqlgroup-bycountduplicates

Answer 1

你似乎想要：

select ip.*
from invoice_payment ip
where exists (select 1
              from invoice_payment ip2
              where ip2.supplier_id = ip.supplier_id and
                    ip2.name = ip.name and
                    ip2.amount = ip.amount and
                    ip2.invoice_number <> ip.invoice_number
             )
order by supplier_id, name, amount;