在mysql中按月分组，即使数据不存在

Question

假设我有一张“销售”的桌子，如下所示

Customer    Date    Amount
Paul    15/01/2015  2000
Jonathan    15/01/2015  3000
Taylor  15/01/2015  2000
Mark    15/01/2015  3000
Paul    15/02/2015  2000
Jonathan    15/02/2015  3000
Paul    25/02/2015  4000
Jonathan    25/02/2015  5000
Jonathan    15/03/2015  2000
Jonathan    25/04/2015  5000
Taylor  25/04/2015  4000
Mark    25/04/2015  5000
Paul    25/05/2015  4000
Jonathan    25/05/2015  5000
Taylor  25/05/2015  4000
Mark    25/05/2015  5000

在报告中我想看到的是-

Customer    Month   Amount
Paul    201501  2000
Paul    201502  6000
Paul    201503  0
Paul    201504  0
Paul    201505  4000

请告诉我怎么做？

更新

Customer    Month   Amount
Paul    201501  2000
Paul    201502  6000
Paul    201503  0
Paul    201504  0
Paul    201505  4000
Jonathan    201501  3000
Jonathan    201502  8000
Jonathan    201503  2000
Jonathan    201504  5000
Jonathan    201505  5000
Taylor  201501  2000
Taylor  201502  0
Taylor  201503  0
Taylor  201504  4000
Taylor  201505  4000
Mark    201501  3000
Mark    201502  0
Mark    201503  0
Mark    201504  5000
Mark    201505  5000

另外，我需要为所有的客户提出这个观点。请告诉我怎么做。

Answer 1

这里有一个解决方案，通过一个演示，使用您提供的日期。此外，还增加了更多的数据进行仿真。

降表销售；

SQL：

-- data
create table sales(Customer varchar(100), `Date` date, Amount int);
insert into sales values
('Paul',    '2015-01-15',  2000),
('Jonathan',    '2015-01-15',  3000),
('Taylor',  '2015-01-15',  2000),
('Mark',    '2015-01-15',  3000),
('Paul',    '2015-02-15',  2000),
('Jonathan',    '2015-02-15',  3000),
('Paul',    '2015-02-25',  4000),
('Jonathan',    '2015-02-25',  5000),
('Jonathan',    '2015-03-15',  2000),
('Jonathan',    '2015-04-15',  5000),
('Taylor',  '2015-04-25',  4000),
('Mark',    '2015-04-25',  5000),
('Paul',    '2015-05-25',  4000),
('Jonathan',    '2015-05-25',  5000),
('Taylor',  '2015-05-25',  4000),
('Mark',    '2015-05-25',  5000);
select * from sales;

-- query wanted
select 
    COALESCE(s.Customer, '') AS Customer, DATE_FORMAT(m2.cont_date, '%Y%m') AS Month, SUM(COALESCE(s.Amount, 0)) AS Amount
from (
    select (select MAX(`Date`) from sales) - interval (year.b * 10 + month.a) MONTH AS cont_date
    from (
        select 0 b union select 1 union select 2 union select 3 union select 4 union 
        select 5 union select 6 union select 7 union select 8 union select 9) AS year
        CROSS JOIN
        (
        select 0 a union select 1 union select 2 union select 3 union select 4 union 
        select 5 union select 6 union select 7 union select 8 union select 9) AS month        
    where (year.b * 10 + month.a) < (select timestampdiff(month, MIN(`Date`), MAX(`Date`)) + 1 from sales) ) m2
    LEFT JOIN sales s ON s.Customer = 'paul' AND DATE_FORMAT(m2.cont_date, '%Y%m') = DATE_FORMAT(s.`Date`, '%Y%m')
GROUP BY Month;

输出：

mysql> select * from sales;
+----------+------------+--------+
| Customer | Date       | Amount |
+----------+------------+--------+
| Paul     | 2015-01-15 |   2000 |
| Jonathan | 2015-01-15 |   3000 |
| Taylor   | 2015-01-15 |   2000 |
| Mark     | 2015-01-15 |   3000 |
| Paul     | 2015-02-15 |   2000 |
| Jonathan | 2015-02-15 |   3000 |
| Paul     | 2015-02-25 |   4000 |
| Jonathan | 2015-02-25 |   5000 |
| Jonathan | 2015-03-15 |   2000 |
| Jonathan | 2015-04-15 |   5000 |
| Taylor   | 2015-04-25 |   4000 |
| Mark     | 2015-04-25 |   5000 |
| Paul     | 2015-05-25 |   4000 |
| Jonathan | 2015-05-25 |   5000 |
| Taylor   | 2015-05-25 |   4000 |
| Mark     | 2015-05-25 |   5000 |
+----------+------------+--------+
16 rows in set (0.00 sec)

mysql>
mysql> -- query wanted
mysql> select
    ->     COALESCE(s.Customer, '') AS Customer, DATE_FORMAT(m2.cont_date, '%Y%m') AS Month, SUM(COALESCE(s.Amount, 0)) AS Amount
    -> from (
    ->     select (select MAX(`Date`) from sales) - interval (year.b * 10 + month.a) MONTH AS cont_date
    ->     from (
    ->         select 0 b union select 1 union select 2 union select 3 union select 4 union
    ->         select 5 union select 6 union select 7 union select 8 union select 9) AS year
    ->         CROSS JOIN
    ->         (
    ->         select 0 a union select 1 union select 2 union select 3 union select 4 union
    ->         select 5 union select 6 union select 7 union select 8 union select 9) AS month
    ->     where (year.b * 10 + month.a) < (select timestampdiff(month, MIN(`Date`), MAX(`Date`)) + 1 from sales) ) m2
    ->     LEFT JOIN sales s ON s.Customer = 'paul' AND DATE_FORMAT(m2.cont_date, '%Y%m') = DATE_FORMAT(s.`Date`, '%Y%m')
    -> GROUP BY Month;
+----------+--------+--------+
| Customer | Month  | Amount |
+----------+--------+--------+
| Paul     | 201501 |   2000 |
| Paul     | 201502 |   6000 |
|          | 201503 |      0 |
|          | 201504 |      0 |
| Paul     | 201505 |   4000 |
+----------+--------+--------+
5 rows in set (0.00 sec)

Answer 2

您可以构建一个包含所有月份的内联表，然后在其上加入您的销售表。

就像这样：

SELECT s.customer_name as Customer,
CONCAT(months.m, '-', years.y) as Month,
s.amount as Amount
    FROM (
       SELECT '01' AS m UNION ALL SELECT '02' UNION ALL SELECT '03' UNION ALL 
       SELECT '04' UNION ALL SELECT '05' UNION ALL SELECT '06' UNION ALL 
       SELECT '07' UNION ALL SELECT '08' UNION ALL SELECT '09' UNION ALL 
       SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '12') AS months
    CROSS JOIN (
       SELECT '2015' AS y UNION ALL SELECT '2016') AS years
    LEFT JOIN sales_table AS s
       ON DATE_FORMAT(s.date, '%Y-%c') = CONCAT(years.y, '-', months.m)
    GROUP BY CONCAT(months.m, '-', years.y)