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 次，如下所示：

左连接输出：

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

我也尝试过INNER JOIN，但这导致购买 0 的客户根本没有显示：

内连接输出：

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

如何在显示购买量为 0 的客户的情况下实现我的期望输出？

标签： mysqlsql

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