mysql - 如果没有找到记录，则返回 0 作为 SUM 结果

Question

我正在尝试总结过去 7 天(实际日期是第 7 天)特定用户每天事件的卡路里。有表 user 和 activities 以及映射表 user_activities。

以下示例针对id=1的用户；

Sum up calories each day last 7 days (this day 7th day) */

SELECT  
    DATE(a.end_time), SUM(a.calories)
FROM
    activities a
JOIN
    user_activities uc ON uc.activity_id = a.id
WHERE
    uc.user_id = 1
    AND DATE(a.end_time) >= CURRENT_DATE - INTERVAL 6 DAY
GROUP BY 
    DATE(a.end_time) DESC

该查询返回此结果集:

2018-12-28   9600
2018-12-27   1200
2018-12-26   1200
2018-12-25   1200
2018-12-24   4800
2018-12-22   1200

这是正确的，但现在我的问题是，正如您在列表中看到的那样，12-23-2018 未列出，因为该日期没有事件。现在我要显示

2018-12-23   0

而不是什么都没有。

怎样才能得到想要的结果？

谢谢你的帮助

我也试过 IFNULL 和 COALESCE 但到目前为止运气不好

Sum up calories each day last 7 days (this day 7th day) */

SELECT  
    DATE(a.end_time), SUM(a.calories)
FROM
    activities a
JOIN 
    user_activities uc ON uc.activity_id = a.id
WHERE 
    uc.user_id = 1
    AND DATE(a.end_time) >= CURRENT_DATE - INTERVAL 6 DAY
GROUP BY
    DATE(a.end_time) DESC

结果:

2018-12-28   9600
2018-12-27   1200
2018-12-26   1200
2018-12-25   1200
2018-12-24   4800
2018-12-22   1200

预期结果:

2018-12-28   9600
2018-12-27   1200
2018-12-26   1200
2018-12-25   1200
2018-12-24   4800
2018-12-23      0
2018-12-22   1200

事件表:

Answer 1

如果您的事件表有可能没有列出特定日期的事件，那么 fa06 的技巧就不会奏效。解决这种情况的一种简单方法是在求和之前添加一些零记录:

SELECT DATE(d), SUM(c)
FROM (
  SELECT  a.end_time as d, a.calories as c
  FROM activities a
  JOIN user_activities uc ON uc.activity_id = a.id
  WHERE uc.user_id = 1
  AND DATE(a.end_time) >= CURRENT_DATE - INTERVAL 6 DAY
  UNION ALL
  SELECT CURRENT_DATE, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 1 DAY, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 2 DAY, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 3 DAY, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 4 DAY, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 5 DAY, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 6 DAY, 0
) z
GROUP BY DATE(d)

fa06 的方法依赖于事件表中每天有一条记录。这是解决问题的有效方法，但您没有具体说明这些表包含哪些数据(提示:发布数据库问题时，请包含每个表中的样本数据)

使用这种方法，我们可以像往常一样进行查找，但我们还会生成 7 个假行，其中包含过去 7 天的日期和 0 卡路里计数。当添加到实际卡路里计数时，这些是无脂肪的；)当没有特定日期的数据时，这些单独提供 0

如果您需要更多天数，请考虑改用行生成模式。 MySQL 没有像其他数据库那样的行生成器，但最简单的技巧是创建 变量，用 0 初始化它然后递增并在从至少 30 行的表返回的每一行上使用它:

SELECT CURRENT_DATE - INTERVAL (@row := @row + 1) DAY as dt, 0 as cal
FROM activities t, (SELECT @row := -1) r
LIMIT 30

这背后的理论是:该表至少有 30 行，@row 变量初始化为 -1，并且存在于查询的 session 范围内。随着行被拉出并返回，@row 递增然后返回(因此它是 0, 1, 2.. )并且这个递增的计数用于从当前数据中减去 0, 1, 2 等天，给我们一个过去 30 天的日期序列

SELECT DATE(d), SUM(c)
FROM (
  SELECT  a.end_time as d, a.calories as c
  FROM activities a
  JOIN user_activities uc ON uc.activity_id = a.id
  WHERE uc.user_id = 1
  AND DATE(a.end_time) >= CURRENT_DATE - INTERVAL 29 DAY
  UNION ALL

  SELECT CURRENT_DATE - INTERVAL (@row := @row + 1) DAY as dt, 0 as cal
  FROM activities t, (SELECT @row := -1) r
  WHERE @row < 30
) z
GROUP BY DATE(d)

请注意，我无法测试这些查询中的任何一个；第二个可能有一些小的语法错误。如果结果不起作用，并且错误很重要/您无法修复，请告诉我。

调试:

单独运行这些查询中的每一个。我不知道这会产生多少行:

  SELECT  a.end_time as d, a.calories as c
  FROM activities a
  JOIN user_activities uc ON uc.activity_id = a.id
  WHERE uc.user_id = 1
  AND DATE(a.end_time) >= CURRENT_DATE - INTERVAL 29 DAY

这个应该产生 30 行。如果不是，那是因为您没有使用至少包含 30 行的表格:

  SELECT CURRENT_DATE - INTERVAL (@row := @row + 1) DAY as dt, 0 as cal
  FROM activities t, (SELECT @row := -1) r
  LIMIT 30

编辑:

修复了此错误 - LIMIT 在合并后应用；这会产生不良结果