mysql - MySQL 的表连接问题

Question

我有一个推荐用户表(包含电子邮件地址和日期列)和一个用户表。

我跑去获取顶级推荐人:

SELECT count(r.Email) as count, r.Email 
FROM refs r 
WHERE r.referredOn > '2011-12-13' 
GROUP BY email 
ORDER BY count DESC

但我想将它与用户表连接起来，以便它与用户表中的其他数据一起显示，我认为连接会起作用。左加入因为电子邮件可能输入错误，有些人将名字等放在 refs.Email 下

SELECT count(r.Email) as count, r.Email, u.* 
FROM refs r LEFT JOIN users u ON u.email_primary = r.Email 
WHERE r.referredOn > '2011-12-13' 
GROUP BY email 
ORDER BY count DESC

上面的查询计数不正确，但我不知道为什么。

Answer 1

试试这个:

SELECT count(r.Email) as count, r.Email 
FROM refs r 
INNER JOIN users u ON u.email_primary = r.Email 
WHERE r.referredOn > '2011-12-13' 
GROUP BY email 
ORDER BY count DESC

如果您从用户 u 添加新列，您还需要将其添加到您的 group by 子句中。

问候