mysql - SQL Null 作为 0 并使用 Left Join？

Question

问题

我有一个日志表，用户可以在其中输入他们在项目上花费的时间。 (它用于应用程序上的图表和数据表)。我还生成了一个“日历”表，以便我可以保留连接日期，并用零填充“空”日期。然而，花了一些时间后，我找不到办法做到这一点......

表 1:日志

id | hoursSpent | logDate 
1    5            2018-08-05
2    1            2018-08-07
3    3            2018-08-10

表 2:日历

id         | db_date
20180807     2018-08-07
20180808     2018-08-08
20180809     2018-08-09
201808010    2018-08-10
etc...

期望的输出

db_date      | hoursSpent
2018-08-05     5
2018-08-06     0
2018-08-07     1
2018-08-08     0
2018-08-09     0
2018-08-10     3

到目前为止使用的代码(并不完全有效......)

SELECT hoursSpent, db_date
FROM logs LEFT JOIN calendar ON db_date
WHERE db_date BETWEEN '2018-08-10' AND '2018-08-01'
GROUP BY db_date

Answer 1

您的连接顺序错误，calendar 应该位于左侧，因为无论 logs 中的匹配行如何，都应包含其所有行。要将 NULL 转换为 0，请使用 coalesce()。如果您按日期GROUP BY，则必须对hoursspent应用聚合函数。我猜你想要 sum()。

SELECT c.db_date,
       sum(coalesce(l.hoursspent, 0)) hoursspent
       FROM calendar c
            LEFT JOIN logs l
                      ON l.logdate = c.db_date
       WHERE c.db_date BETWEEN '2018-08-10'
                               AND '2018-08-01'
       GROUP BY c.db_date;

这里您还可以省略 coalesce()，因为 MySQL 的 sum() 无论如何都会将 NULL 视为 0 .

但是示例数据表明，如果同一天不存在多个日志条目，那么您可能根本不需要GROUP BY。

SELECT c.db_date,
       coalesce(l.hoursspent, 0) hoursspent
       FROM calendar c
            LEFT JOIN logs l
                      ON l.logdate = c.db_date
       WHERE c.db_date BETWEEN '2018-08-10'
                               AND '2018-08-01';