SELECT a.item_code,
COALESCE(SUM(b.sd_qty), 0) total,
DATE_FORMAT(d.sd_date, '%m-%Y') month_year
FROM item a
CROSS JOIN (
SELECT DISTINCT sd_date
FROM sales_details
WHERE sd_date >= '2020-04-01' - INTERVAL 3 MONTH AND sd_date
或者,對於 MySql 8.0 ,使用遞歸 CTE 傳回您想要結果的所有月份的開始日期,可以交叉連接到表:
WITH RECURSIVE dates AS (
SELECT '2020-04-01' - INTERVAL 3 MONTH AS sd_date
UNION ALL
SELECT sd_date INTERVAL 1 MONTH
FROM dates
WHERE sd_date INTERVAL 1 MONTH
您也應該將表格交叉連接到不同的日期: