finding multi column duplicates mysql

How can i find the duplicate records for multi column ? Table has primary key (Auto increment)

EG

 ID  a_id  b_id
 ---- ---- ------
   1    34   23
   2    34   23
   3    35   25

for example i want to find records with same a_id an开发者_开发技巧d b_id...

Thanks

select t.ID, t.a_id, t.b_id
from (
  select a_id, b_id
  from tbl
  group by a_id, b_id
  having count(*) > 1) x, tbl t
where x.a_id = t.a_id and x.b_id = t.b_id
order by t.a_id, t.b_id

This query will show you all the duplicates on the tuple (a_id, b_id)

SELECT r1.id, r2.id
FROM test r1, test r2
WHERE r1.id < r2.id
AND r1.a_id = r2.a_id
AND r1.b_id = r2.b_id

edit:

Misread what you were looking for. You can find duplicate entries on your table using the group by argument for select as stated above or even a self join.

mysql> SELECT * FROM my_table;
+------+------+------+
| ID   | a_id | b_id |
+------+------+------+
|    1 |   34 |   23 |
|    2 |   34 |   23 |
|    3 |   35 |   25 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> SELECT t1.ID source_row, 
    ->        t2.ID same_as_row, 
    ->        t1.a_id a_id, 
    ->        t1.b_id b_id 
    ->   FROM my_table t1 
    ->   JOIN my_table t2 
    ->     ON t1.a_id = t2.a_id 
    ->    AND t1.b_id = t2.b_id 
    ->    AND t1.ID != t2.ID;

+------------+-------------+------+------+
| source_row | same_as_row | a_id | b_id |
+------------+-------------+------+------+
|          2 |           1 |   34 |   23 |
|          1 |           2 |   34 |   23 |
+------------+-------------+------+------+
2 rows in set (0.00 sec)

edit2:

mysql> SELECT * FROM my_table;
+------+------+------+
| ID   | a_id | b_id |
+------+------+------+
|    1 |   34 |   23 | # same as 2, 4
|    2 |   34 |   23 | # same as 1, 4
|    3 |   35 |   25 |
|    4 |   34 |   23 | # same as 1, 2
|    5 |   31 |   23 |
+------+------+------+

mysql>   SELECT t1.ID source_row, 
    ->          t2.ID same_as_row, 
    ->          t1.a_id a_id, 
    ->          t1.b_id b_id 
    ->     FROM my_table t1 
    ->     JOIN my_table t2 
    ->       ON t1.a_id = t2.a_id 
    ->      AND t1.b_id = t2.b_id 
    ->      AND t1.ID != t2.ID
    -> ORDER BY source_row;
+------------+-------------+------+------+
| source_row | same_as_row | a_id | b_id |
+------------+-------------+------+------+
|          1 |           2 |   34 |   23 |
|          1 |           4 |   34 |   23 |
|          2 |           1 |   34 |   23 |
|          2 |           4 |   34 |   23 |
|          4 |           1 |   34 |   23 |
|          4 |           2 |   34 |   23 |
+------------+-------------+------+------+
6 rows in set (0.00 sec)

It's a basic SQL query :

SELECT *
FROM my_table
WHERE a_id = b_id;

where my_table is the name of your table.