mysql - 填充空的 MySQL 结果

Question

我想为销售设定一个连续的日期。

SELECT *,
   UNIX_TIMESTAMP(calendar.datefield) * 1000 AS time,
   IFNULL(Sum(mos + mosnk), 0)               AS mosfinal,
   IFNULL(Sum(neukunden), 0)                 AS neukunden
FROM   sms_stats
   RIGHT JOIN calendar
           ON ( DATE(sms_stats.date) = calendar.datefield )
WHERE  calendar.datefield BETWEEN Curdate() - INTERVAL 90 day AND Now()
GROUP  BY Date_format(calendar.datefield, '%Y%m%d') 

这会返回过去 90 天的列表。现在我想过滤它，但如果我这样做

WHERE owner = 2 AND calendar.datefield BETWEEN Curdate() - INTERVAL 90 day AND Now()

它只返回一个结果，而不是日期列表。

Answer 1

where 子句中的条件“撤消”右外连接。解决方案是将条件移至 where 子句。我更喜欢左外连接而不是右外连接，所以我将交换表:

SELECT *,
       UNIX_TIMESTAMP(c.datefield) * 1000        AS time,
       IFNULL(Sum(mos + mosnk), 0)               AS mosfinal,
       IFNULL(Sum(neukunden), 0)                 AS neukunden
FROM calendar c LEFT JOIN
     sms_stats ss
     ON DATE(ss.date) = c.datefield  and
        ss.owner = 2
WHERE c.datefield BETWEEN Curdate() - INTERVAL 90 day AND Now()
GROUP BY Date_format(c.datefield, '%Y%m%d') ;

我添加了表别名以使查询更具可读性。