Get duplicate entries from a table

How can I get rows that were inserted more than once from a table? I allow my site users to have only one account per email but my script had a little bug so users where able to register more than once with the same email address. I want to get all the emails that has been recorded more than once. How is that possible 开发者_JS百科using php/mysql?

---

You may want to try something like the following:

SELECT      email_address, 
            COUNT(*) number_of_dupes, 
            GROUP_CONCAT(user_id) users
FROM        users
GROUP BY    email_address
HAVING      COUNT(*) > 1;

Test case:

CREATE TABLE users (user_id int, email_address varchar(30));

INSERT INTO users VALUES (1, 'info1@example.com');
INSERT INTO users VALUES (2, 'info2@example.com');
INSERT INTO users VALUES (3, 'info1@example.com');
INSERT INTO users VALUES (4, 'info2@example.com');
INSERT INTO users VALUES (5, 'info3@example.com');
INSERT INTO users VALUES (6, 'info2@example.com');

Result:

+-------------------+-----------------+-------+
| email_address     | number_of_dupes | users |
+-------------------+-----------------+-------+
| info1@example.com |               2 | 1,3   |
| info2@example.com |               3 | 2,4,6 |
+-------------------+-----------------+-------+
2 rows in set (0.00 sec)

---

select email from table group by email having count(email) > 1
should do the trick :)

---

Use this query:

SELECT email FROM tableName GROUP BY email HAVING count(email) > 1