MySQL LEFT JOIN SUM 不包括 0

Question

我猜这个问题有很多变体，但这有一个转折。

我的主表包含某些用户在某些日期记录的公里数:

表km_run:

|entry|mnumber|dato |km | 其中“dato”是具体日期。格式如下:

|1 |3 |2013-01-01|5.7|

对于特定用户('mnumber')，我想计算一年中每周的总和。为此，我制作了一个“虚拟表”，其中仅包含从 1 到 53 的周数:

Table `week_list`:
|week|
|1   |
|2   |

等等..

此查询给出了总和，但是如果特定周的“km_run”中没有条目，我找不到返回零的方法。

SELECT `week_list`.`week`, WEEKOFYEAR(`km_run`.`dato`),  SUM(`km_run`.`km`)
FROM `week_list` LEFT JOIN `km_run` ON  WEEKOFYEAR(`dato`) = `week_list`.`week`
WHERE `km_run`.`mnumber` = 3 AND `km_run`.`dato` >= '2013-01-01'
 AND `km_run`.`dato` < '2014-01-01'
GROUP BY WEEKOFYEAR(`dato`)

我尝试过 COALESCE( SUM(km),0) 并且我还尝试在总和周围使用 IFNULL 函数。尽管有左连接，但 SQL 语句中并未返回 week_list 中的所有记录。

结果如下:

week | WEEKOFYEAR(`km_run`.`dato`) | SUM(`km_run`.`km`)
1    | 1                           | 58.4
3    | 3                           | 50.7
4    | 4                           | 39.2

如您所见，第二周被跳过，而不是返回 0

Answer 1

首先 JOIN 起作用，创建这样的行:
week=2 weekofyear=null mnumber=null sum=0 ...
然后，WHERE 子句(例如 where mnumber=3)排除包含 null 的行。你可以尝试这样的事情:

SELECT week, SUM(km) FROM (
    (SELECT km_run.km AS km, WEEKOFYEAR(km_run.dato) AS week
    FROM km_run
    WHERE mnumber = 3 AND km_run.dato >= '2013-01-01' AND km_run.dato < '2014-01-01')
    UNION
    (SELECT 0 AS km, week_list.week as week FROM week_list)
) GROUP BY week