Legen Sie den Standardwert für das MySQL-Verbindungsintervall Jahr und Monat fest

Question

Es gibt ein Problem mit meiner Anfrage. Ich habe zwei Tabellen und möchte sie verknüpfen, um Ergebnisse basierend auf dem Primärschlüssel der ersten Tabelle zu erhalten, aber mir fehlt ein Datenelement aus der ersten Tabelle.

Das ist meine Geige

Wie Sie sehen, fehlt mir „xx3“ für Monat 1

Ich habe versucht, die linken und rechten Verbindungen zu ändern, aber immer noch das gleiche Ergebnis. Wie Sie sehen, muss ich coalesce(sum(b.sd_qty),0) 设置为总计， 如果没有 qty，则将 0 auf den Standardwert setzen.

Answer 1

您还应该将表交叉连接到不同的日期：

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 < '2020-05-01'
) d 
LEFT JOIN sales_details b 
ON a.item_code = b.item_code AND b.sd_date = d.sd_date
GROUP BY month_year, a.item_code
ORDER BY month_year, a.item_code;

或者，对于 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 < '2020-05-01'
)
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 dates d 
LEFT JOIN sales_details b 
ON a.item_code = b.item_code AND DATE_FORMAT(b.sd_date, '%m-%Y') = DATE_FORMAT(d.sd_date, '%m-%Y')
GROUP BY month_year, a.item_code
ORDER BY month_year, a.item_code;

查看演示。