mysql日期序列填充_MySQL如何在范围内填充缺失的日期？

我不喜欢其他的答案，要求创建表格等等。这个查询在没有helper表的情况下有效地执行。SELECT

IF(score IS NULL, 0, score) AS score,

b.Days AS date

FROM

(SELECT a.Days

FROM (

SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days

FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a

CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b

CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c

) a

WHERE a.Days >= curdate() - INTERVAL 30 DAY) b

LEFT JOIN your_table

ON date = b.Days

ORDER BY b.Days;

所以让我们来分析一下。SELECT

IF(score IS NULL, 0, score) AS score,

b.Days AS date

if将检测没有得分的天数并将其设置为0。b、 Days是您从当前日期选择的配置天数，最多1000天。(SELECT a.Days

FROM (

SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days

FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a

CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b

CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c

) a

WHERE a.Days >= curdate() - INTERVAL 30 DAY) b

这个子查询是我在stackoverflow上看到的。它有效地从当前日期生成过去1000天的列表。结尾WHERE子句中的间隔(当前为30)决定返回哪些天；最大值为1000。这个查询可以很容易地修改为返回值为100年的日期，但1000年应该对大多数事情都有好处。LEFT JOIN your_table

ON date = b.Days

ORDER BY b.Days;

这是将包含分数的表放入其中的部分。与日期生成器查询中的选定日期范围进行比较，以便能够在需要时填充0(分数最初将设置为NULL，因为它是LEFT JOIN；这在select语句中是固定的)。我也按日期点，只是因为。这是您的偏好，您也可以按分数订购。

在ORDER BY之前，您可以很容易地加入到您的表中，其中包含您在编辑时提到的用户信息，以添加最后一个需求。

我希望这个版本的查询可以帮助某些人。谢谢你的阅读。