mysql 手动写时间_MySQL如何在范围内填写缺失日期？

白板的微信

我不喜欢其他答案，要求创建表格等等。这个查询在没有助手表的情况下有效地完成了它。SELECT     IF(score IS NULL, 0, score) AS score,    b.Days AS dateFROM     (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) bLEFT JOIN your_table    ON date = b.DaysORDER BY b.Days;所以让我们解剖一下这个。SELECT     IF(score IS NULL, 0, score) AS score,    b.Days AS dateif将检测没有得分的天数，并将其设置为0。日期是您从当前日期(最多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这个子查询是我在堆栈溢出上看到的。它有效地从当前日期生成过去1000天的列表。末尾WHERE子句中的间隔(当前为30)确定返回的日期；最大值为1000。此查询可以很容易地修改为返回值为1000年的日期，但对于大多数情况来说，1000应该是好的。LEFT JOIN your_table    ON date = b.DaysORDER BY b.Days;这是把你的表中包含分数的部分。将所选日期范围与从日期生成器查询到的选定日期范围进行比较，以便能够在需要时填写0(得分将设置为NULL最初，因为它是LEFT JOIN这在SELECT语句中是固定的)。我也是按日期点的，只是因为。这是偏好，你也可以按分数订购。在ORDER BY您可以很容易地加入您的表格，有关您提到的用户信息与您的编辑，以添加最后的要求。我希望这个版本的查询能帮助到一些人。谢谢你的阅读。