php - MySQL 需要在 join 右表无值时显示 0，带累积

Question

我有一个表，其中列出了针对产品代码的每月销售数量，另一个表则列出了可以在销售月份之前或之后延伸的月份列表。我需要结果显示 0 销售(如果有)当月没有销售，并且累计将其加起来。我尝试过使用 case 和 if 并在 sales.sales 为 null 时将其设置为 0，但这不起作用，我仍然只有空白。

    create table summary as (SELECT
   q1.productid As productid,
   q1.date AS Month_View,
   q1.sales AS Monthly_Units_Sold,
   (@runtot_sales := @runtot_sales + q1.sales) AS Cumulative_Sales
FROM
   (SELECT
   sales.productid,
dates.date,
if(sales.date is null,0,sales.sales) as sales
from
dates 
left join sales on dates.date = sales.date
where
sales.productid = '$input1'
group by dates.date
    ORDER  BY date) AS q1);
    ";

Answer 1

尝试COALESCE()函数返回列表的第一个非NULL值Also see demo here

CREATE TABLE summary AS 
(SELECT 
  q1.productid AS productid,
  q1.date AS Month_View,
  q1.sales AS Monthly_Units_Sold,
  (
    @runtot_sales := @runtot_sales + q1.sales
  ) AS Cumulative_Sales 
FROM
  (SELECT 
    sales.productid,
    dates.date,
    COALESCE(sales.sales, 0) AS sales 
  FROM
    dates 
    LEFT JOIN sales 
      ON dates.date = sales.date 
  WHERE sales.productid = '$input1' 
  GROUP BY dates.date 
  ORDER BY DATE) AS q1) ;

MySQL COALESCE() function