MySql LEFT加入COUNT [英] MySql LEFT JOIN with COUNT

问题描述

我有两个表，客户和销售。我想计算每个客户的销售额，并为每个商店创建一个每月销售额表。

I have two tables, customers and sales. I want to count sales for each customer and create a table of sales per month for each store.

我想生产类似的东西;

I would like to produce something like;

------------------------------
month  |  customers  | sales  |
------------------------------
1/2013 |      5      |   2    |
2/2013 |      21     |   9    |
3/2013 |      14     |   4    |
4/2013 |      9      |   3    |

但我在使用以下方式时无法获得正确的销售计数;

but I am having trouble getting the sales count to be correct when using the following;

SELECT CONCAT(MONTH(c.added), '/', YEAR(c.added)), count(c.id), count(s.id)
FROM customers c
LEFT JOIN sales s 
ON s.customer_id = c.id AND MONTH(c.added) = MONTH(s.added) AND YEAR(c.added) = YEAR(s.added)
WHERE c.store_id = 1
GROUP BY YEAR(c.added), MONTH(c.added);

客户表;

-------------------------------
id    |   store_id  | added    |
-------------------------------
1     |      1      |2013-02-01 |
2     |      1      |2013-02-02 |
3     |      1      |2013-03-16 |

销售表;

---------------------------------
id    |   added    | customer_id |
---------------------------------
1     | 2013-02-18 |     3       |
2     | 2013-03-02 |     2       |
3     | 2013-03-16 |     3       |

任何人都可以在这里工作？

Can anyone help here?

推荐答案

（更新）现有查询只会计算在添加客户的同一个月内完成的销售。尝试此操作：

(Updated) The existing query will only count sales made in the same month that the customer was added. Try this, instead:

SELECT CONCAT(MONTH(sq.added), '/', YEAR(sq.added)) month_year,
       sum(sq.customer_count), 
       sum(sq.sales_count)
FROM (select s.added, 0 customer_count, 1 sales_count
      from customers c
      JOIN sales s ON s.customer_id = c.id
      WHERE c.store_id = 1
      union all
      select added, 1 customer_count, 0 sales_count
      from customers
      WHERE store_id = 1) sq
GROUP BY YEAR(sq.added), MONTH(sq.added);

这篇关于MySql LEFT加入COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！