MYSQL如何在一个查询中计算相关记录数量？

Question

我有一个MySQL查询，查询了一个名为“contacts”的表格，每个联系人都有购买记录。购买记录在另一张相关的表格里。我想要像这样把每个联系人的名字和他们所做购买数量显示在右边：

Adam(1)
Mike(8)
Steve(3) 

我的当前 SQL 语句如下：

SELECT * FROM contacts ORDER BY contacts.name ASC"

我的当前表格看起来像这样：

Adam
Mike
Steve

为了将相关表（purchases）的计数拉入当前表中，我知道必须以某种方式加入“purchases”表，然后使用GROUP BY和count()函数，但我不确定如何构造这个sql语句。有人能帮我吗？

再次强调，我只是想列出一个表（contacts）并统计其相关记录（purchases），使其看起来像这样：

Adam(1)
Mike(8)
Steve(3)

非常感谢您的任何帮助。

- user982853

Answer 1

假设 purchases 表与 contacts 表有一个外键关联，该外键称为 contact_id，那么应该可以使用类似以下的查询语句：

SELECT c.name, count(p.contact_id)
FROM contacts AS c
LEFT OUTER JOIN purchases AS p
ON p.contact_id = c.contact_id
GROUP BY c.contact_id
ORDER BY c.name ASC

我建议使用这个查询将计数放在一个单独的列中。如果你必须按照你指定的方式以名称后面加括号的方式进行格式化，你可以使用MySQL的concat()函数来实现。像这样：

SELECT concat(c.name, '(', count(p.contact_id), ')') AS Name_And_Count_Together
FROM contacts AS c
LEFT OUTER JOIN purchases AS p
ON p.contact_id = c.contact_id
GROUP BY c.contact_id
ORDER BY c.name ASC

- Asaph

---

非常感谢，那太有效了。很高兴我在这里发帖，因为我自己永远想不出来。再次感谢您。 - user982853

能否在第一个查询中添加最后购买日期的信息？ - ipel

@ipel 当然可以。您可以在选择列表中添加类似于max(p.purchase_date)的内容。 - Asaph

如果你想知道LEFT JOIN和LEFT OUTER JOIN是否相同，根据这篇SO帖子，它们是相同的：https://dev59.com/e2Up5IYBdhLWcg3wNVlb - AdrianCR

Answer 2

  SELECT contacts.name, COUNT(*) 
    FROM contacts, purchases 
   WHERE contacts.name = purchases.name
GROUP BY purchases.key
ORDER BY contacts.name

在 WHERE 子句中，将 .name 替换为您用于标识记录的键。

- kz3

---

建议使用外连接，这样您就可以包括那些没有购买记录的联系人。 - Asaph

Answer 3

所需的查询应该是这样的（我不知道你的模式）：

SELECT c.name, count(p.id) FROM contacts c
JOIN purchases p ON (c.id = p.id)
GROUP BY p.id
ORDER BY contacts.name ASC

- z-index

---

建议使用外连接，这样您可以包括那些没有购买记录的联系人。 - Asaph