mysql - mysql选择字段的过滤计数以及表中同一字段的总行

我有一张顾客和商店的桌子。商店代表他们从我这里购买东西的地方。我想计算他们从每个商店购买商品的次数，以及他们从任何商店购买商品的总次数。

样品表称为PURCHASE：

Customer    Store
ABC         Store 1
ABC         Store 2
ABC         Store 2
ABC         Store 3
ABC         Store 3
ABC         Store 4
ABC         Store 4
ABC         Store 4

The results I want:

Customer    Store       Count   Total
ABC         Store 1       1       8
ABC         Store 2       2       8
ABC         Store 3       2       8
ABC         Store 4       3       8

I tried something like this but it took a long time as there are over 40k records:

SELECT a.customer, store, count(a.customer) AS thisstore
FROM PURCHASE a
LEFT JOIN
    (
    SELECT COUNT(customer) AS totalsales,customer
    FROM PURCHASE
    GROUP BY customer) b
ON b.customer=N.customer GROUP BY a.customer;

如果有人知道更好的方法，请告诉我-谢谢。

最佳答案

此查询将为您提供所需的输出，并且如果您在PURCHASE.customer上具有索引，该查询应该会更快：

SELECT a.customer, a.store, count(*) AS thisstore
, (select count(*) from PURCHASE b where b.customer=a.customer) as total
FROM PURCHASE a
GROUP BY a.customer,a.store

关于mysql - mysql选择字段的过滤计数以及表中同一字段的总行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38704504/