How to find duplicate field combo

问题:

I have a table with columns id, a, and b.

a+b should be unique, but this is a legacy database that is not constrained correctly. How can I get a set of ids for records in which a+b is not unique?

If I have

ID A B
1  2 3
2  2 3
3  1 3
4  1 4

Then I want to get records 1 and 2 back from the query.

回答1:

select
    id, a, b

from your_table t

join (select a, b from your_table group by a, b having count(1) > 1) dup on dup.a = t.a and dup.b = t.b

回答2:

To get back rows 1 and 2 (as you stated), use this:

SELECT
  *
FROM
  your_table
  INNER JOIN (
    SELECT a, b FROM your_table GROUP BY a, b HAVING COUNT(*) > 1
  ) dupes ON 
    your_table.a = dupes.a AND
    your_table.b = dupes.b

回答3:

Slightly faster using window functions:

select *
from (
     select
        a
     ,  b
     ,  cnt = count(*) over ( partition by a, b )
     from your_table
) x
where cnt > 1; /* Dupe */