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

Question

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

我有一张有两列，日期和分数的桌子。在过去的30天中，它最多有30条目。

date      score

-----------------

1.8.2010  19

2.8.2010  21

4.8.2010  14

7.8.2010  10

10.8.2010 14

我的问题是有些日期不见了-我想看看：

date      score

-----------------

1.8.2010  19

2.8.2010  21

3.8.2010  0

4.8.2010  14

5.8.2010  0

6.8.2010  0

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