获得最后6个月的零值销售

Question

我使用下面的查询来获得数据库最后6个月销售额的总和。

SELECT
    pharmacy_bulk_issue_details.product_id,
    COALESCE(
        (
            SUM(
                pharmacy_bulk_issue_details.qty - pharmacy_bulk_issue_details.returened_qty
            )
        ),
        0
    ) AS unit_qty,
    DATE_FORMAT(
        pharmacy_bulk_issue.added_time,
        '%Y %M'
    ) AS MONTH
FROM
    pharmacy_bulk_issue_details
LEFT JOIN pharmacy_bulk_issue ON pharmacy_bulk_issue_details.bulk_id = pharmacy_bulk_issue.id
WHERE
    pharmacy_bulk_issue_details.product_id = '00000004357' AND pharmacy_bulk_issue.added_time >= NOW() - INTERVAL '6' MONTH
GROUP BY
    pharmacy_bulk_issue_details.product_id, DATE_FORMAT(
        pharmacy_bulk_issue.added_time,
        '%Y %M'
    )

一切正常，但是，假设上个月没有出售特定项目，那么查询将不会显示该月份为零。它只是被排除在外，结果将是5行。我如何才能得到零值行，为未售出的月份？

Answer 1

日历表可能如下所示(考虑到您是按月分组)：

WITH recursive calendar as (
   select min(pharmacy_bulk_issue.added_time) as d 
   from pharmacy_bulk_issue_details

   union all

   select date_add(d, INTERVAL 1 MONTH) 
   from calendar 
   where d <= (select max(pharmacy_bulk_issue.added_time) as d 
               from pharmacy_bulk_issue_details)
)
select * from calendar;

编辑：除了更新之外，还有一种方法可以实现，请参阅：如何将MSSQL查询转换为MySQL 5.7？

set @r=0;

select (SELECT DATE_ADD(MIN(added_time), INTERVAL -DAY(MIN(added_time))+1 DAY)
        FROM pharmacy_bulk_issue) as d
union 
  
SELECT DISTINCT DATE_ADD(DATE_ADD(added_time, INTERVAL R MONTH),  INTERVAL -DAY(added_time)+1 DAY) as d
from pharmacy_bulk_issue
cross join (select @r:=@r+1 as R from information_schema.tables t) x
where date_add(added_time, INTERVAL R MONTH) < (SELECT MAX(added_time) 
                                                FROM pharmacy_bulk_issue);

请参阅：DBFIDDLE