Question

我怎么能从一张桌子上接过不止一次的囚室? 我允许我的网站用户每个电子邮件只开一个账户,但我的发言稿只有很少浏览,因此用户能够用同一个电子邮件地址登记不止一次。我想得到不止一次记录的所有电子邮件。如何使用php/mysql?

Answer 1

您不妨尝试如下内容:

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

试验:

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 );

结果:

+-------------------+-----------------+-------+
| 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)

Answer 2

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

Answer 3

利用这一询问:

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

友情链接