mysql - SQL - LEFT JOIN，但我希望 COUNT(*) 只计算连接的 INNER 部分的结果

Question

我想显示每个客户的购买数量。如果他们进行了 0 次购买，我想显示 0。

期望的输出:

 -------------------------------------
| customer_name | number_of_purchases |
 -------------------------------------
|    Marg       |          0          |
|    Ben        |          1          |
|    Phil       |          4          |
|    Steve      |          0          |
 -------------------------------------

客户表:

 -----------------------------
| customer_id | customer_name |
 -----------------------------
|      1      |      Marg     |
|      2      |      Ben      |
|      3      |      Phil     |
|      4      |      Steve    |
 -----------------------------

购买表:

 --------------------------------------------------
| purchase_id | customer_id | purchase_description |
 --------------------------------------------------
|      1      |       2     |     500 Reams        |
|      2      |       3     |     6 Toners         |
|      3      |       3     |     20 Staplers      |
|      4      |       3     |     2 Copiers        |
|      5      |       3     |     9 Name Plaques   |
 --------------------------------------------------

---

我目前的查询如下:

SELECT customer_name, COUNT(*) AS number_of_purchaes 
FROM customer 
LEFT JOIN purchases ON customer.customer_id = purchases.customer_id 
GROUP BY customer.customer_id

但是，由于它是 LEFT JOIN，因此查询会生成未购买客户的行，这使它们成为 COUNT(*) 的一部分。换句话说，购买了 0 次的客户显示为购买了 1 次，如下所示:

LEFT JOIN 输出:

 -------------------------------------
| customer_name | number_of_purchases |
 -------------------------------------
|    Marg       |          1          |
|    Ben        |          1          |
|    Phil       |          4          |
|    Steve      |          1          |
 -------------------------------------

我也尝试过 INNER JOIN，但这会导致购买次数为 0 的客户根本不显示:

INNER JOIN 输出:

 -------------------------------------
| customer_name | number_of_purchases |
 -------------------------------------
|    Ben        |          1          |
|    Phil       |          4          |
 -------------------------------------

我如何才能实现我的期望输出，其中显示购买次数为 0 的客户？

Answer 1

而不是 count(*) 使用 count(purchase_id)

SELECT customer_name, COUNT(purchase_id) AS number_of_purchaes 
FROM customer 
LEFT JOIN purchases ON customer.customer_id = purchases.customer_id 
GROUP BY customer_id,customer_name