mysql - SQL - 获取表一的所有行和行数

Question

如何创建一个 SQL 查询来获取表中的所有行以及在电子邮件下插入的行数？

我试过类似的方法，但是这对行进行了分组，所以我没有得到所有的行。

SELECT *, COUNT(email) AS 'count' FROM adverts GROUP BY email

Answer 1

select a1.*, a2.count
from adverts a1
join
(
  SELECT email, COUNT(*) AS 'count' 
  FROM adverts 
  GROUP BY email
) a2 on a1.email = a2.email