mysql 计算重复项

Question

我有一张这样的 table

mysql> desc user_changes;
+----------+--------------+------+-----+---------+-------+ 
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+ 
| id       | varchar(16)  | NO   | PRI |         |       | 
| email    | varchar(255) | YES  | MUL | NULL    |       |  
| products | longtext     | YES  |     | NULL    |       | 
+----------+--------------+------+-----+---------+-------+ 
3 rows in set (0.00 sec)

我需要创建一个查询来计算所有通过电子邮件发送的重复项

 | 20061129180346 | test@test.com | 1^31^9 
 | 20061129330638 | test@test.com | 1^31^9

Answer 1

SELECT    count(*), email
FROM      user_changes
GROUP BY  email
HAVING    count(*) > 1

最后一个 (HAVING) 子句将选择限制为 >1 的电子邮件计数，例如重复。