MySQL计算重复行数

Question

我有一个类似这样的表格：

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 | [e-mail] | 1^31^9 
 | 20061129330638 | [e-mail] | 1^31^9

- Matt Elhotiby

---

你需要帮忙吗，还是只是想让别人替你做？ - Michael Todd

Answer 1

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

最后的 (HAVING) 子句限制了选择电子邮件计数大于1的记录，例如重复记录。

- DVK

Answer 2

select
    email,
    count(email) as email_count
from
    user_changes
group by
    email
having
    count(email) > 1
order by
    email asc

- mellamokb

Answer 3

我不理解你的第二个代码块，但是要获取每个电子邮件的计数，请执行以下操作

SELECT email, COUNT(*) AS count FROM user_changes GROUP BY email WHERE count > 1;

- David Gillen

Answer 4

这将计算表中每个电子邮件地址的数量，忽略电子邮件地址的大小写。例如，“[e-mail]”和“[e-mail]”将被视为相同的电子邮件地址。它还仅显示重复项，任何仅出现一次的电子邮件地址都不会返回。如果您也想返回这些电子邮件地址，请省略“HAVING”子句。

SELECT LOWER(email) EmailAddress, COUNT(*) EmailCount
  FROM user_changes
 GROUP BY LOWER(email)
HAVING COUNT(*) > 1;

- BG100

Answer 5

select email, count(*)
from user_changes
group by email
having count(*) > 1

- Martin Smith