mysql - 使用 JOIN 计算列中的所有值

Question

我正在连接三个表，需要返回两个单独的计数，一个显示已购买商品的唯一用户总数，另一个显示未购买商品的唯一用户总数。为简洁起见，对这些进行了裁剪，但这里是相关表格:

user table
+----------+------+------+-----+
| username | colb | colc | etc |
+----------+------+------+-----+
| user1    | *    | *    | *   |
| user2    | *    | *    | *   |
| user3    | *    | *    | *   |
+----------+------+------+-----+

purchase table
+------------+---------+----------+------+
| purchaseID | storeID | username | cost |
+------------+---------+----------+------+
|          1 |       1 | user1    | *    |
|          2 |       1 | user2    | *    |
|          3 |       5 | user2    | *    |
|          4 |       3 | user1    | *    |
+------------+---------+----------+------+

store table
+---------+-----------+-----+
| storeID | storeName | etc |
+---------+-----------+-----+
|       1 | store1    | *   |
|       2 | store2    | *   |
|       3 | store3    | *   |
+---------+-----------+-----+

我目前正在使用此查询来获取从商店购买商品的唯一身份用户:

SELECT 
    store.storeID storeID, 
    store.storeName storeName, 
    COUNT(DISTINCT CASE WHEN purchase.username IS NOT NULL
                        THEN purchase.purchaseID END) AS purchases
    [Query to retrieve total unique users who have not purchased an item]
FROM store 
LEFT JOIN purchase
    ON store.storeID = purchase.storeID
LEFT JOIN user
    ON purchase.username = user.username
GROUP BY 1, 2

我尝试了几种不同的方法，但都没有奏效。我发现的问题是当 LEFT JOIN 发生时它只返回用户名的匹配结果，因此 COUNT 不会包括用户表中的其他用户。我没有运气找到解决这个问题的方法，所以我希望这里有人能帮我一把。我希望看到的结果应该是这样的:

+---------+-----------+-----------+--------------+
| storeID | storeName | purchases | nonPurchases |
+---------+-----------+-----------+--------------+
|       1 | store1    |         2 |            1 |
|       2 | store2    |         0 |            3 |
|       3 | store3    |         1 |            2 |
+---------+-----------+-----------+--------------+

Answer 1

其实很简单

首先，您计算所有用户并减去不同购买者的数量

SELECT 
    store.storeID storeID, 
    store.storeName storeName, 
    COUNT(DISTINCT CASE WHEN purchase.username IS NOT NULL
                        THEN purchase.purchaseID END) AS purchases,
   (SELECT COUNT(*) FROM User) - COUNT(DISTINCT CASE WHEN purchase.username IS NOT NULL
                        THEN purchase.purchaseID END) AS NON_purchases
FROM store 
LEFT JOIN purchase
    ON store.storeID = purchase.storeID
LEFT JOIN user
    ON purchase.username = user.username
GROUP BY 1, 2