MySQL如何在范围内填写缺失日期？

Question

MySQL如何在范围内填写缺失日期？我有一张有两列，日期和分数的桌子。在过去的30天中，它最多有30条目。date      score-----------------1.8.2010  192.8.2010  214.8.2010  147.8.2010  1010.8.2010 14我的问题是有些日期不见了-我想看看：date      score-----------------1.8.2010  192.8.2010  213.8.2010  04.8.2010  145.8.2010  06.8.2010  07.8.2010  10...我需要从单个查询中得到：19，21，9，14，0，0，10，0，0，14.这意味着缺少的日期被0填充。我知道如何获得所有的值，并在服务器端语言中迭代日期和遗漏空白。但是在MySQL中是否可以这样做，这样我就可以按日期对结果进行排序，并得到丢失的部分。编辑：在这个表中有一个名为userid的列，所以我有30.000个用户，其中一些用户在这个表中得分。我删除每天的日期，如果日期<30天前，因为我需要最后30天的得分为每个用户。原因是我正在绘制一个用户在过去30天的活动的图表，并绘制一个图表，我需要用逗号分隔的30个值。所以我可以在查询中告诉我userid=10203活动，这个查询会给我30分，在过去的30天中每个都有一个。我希望我现在说的更清楚了。

Answer 1

您可以通过使用日历表..这是一个表，您只创建一次并填充一个日期范围(例如，2000-2050年期间每天有一个数据集；这取决于您的数据)。然后，您可以根据日历表对表进行外部连接。如果表中缺少日期，则返回0作为得分。

Answer 2

我不喜欢其他答案，要求创建表格等等。这个查询在没有助手表的情况下有效地完成了它。

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。日期是您从当前日期(最多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.Days

ORDER BY b.Days;

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

在ORDER BY您可以很容易地加入您的表格，有关您提到的用户信息与您的编辑，以添加最后的要求。

我希望这个版本的查询能帮助到一些人。谢谢你的阅读。