通过 MySQL 从数据库中检索丢失的日期
所以我有一个表格，用于收集我所做工作的数据。每次创建工作时，我都会为其分配一个日期。问题是我没有工作的日子不会存储在数据库中，因此当我绘制数据图表时，我永远不会看到我有零工作的日子。
我当前的查询如下所示：
SELECT job_data_date, SUM(job_data_invoice_amount) as job_data_date_income
FROM job_data
WHERE job_data_date >= '2010-05-05'
GROUP BY job_data_date
ORDER BY job_data_date;
输出为：
| job_data_date | job_data_date_income |
| 2010-05-17 | 125 |
| 2010-05-18 | 190 |
| 2010-05-20 | 170 |
正如您从示例输出中看到的，2010-05-19 不会显示在结果中，因为它从未存储在那里。
有没有办法显示缺失的日期？
谢谢你，
马拉
So I have a table where I collect data for the jobs that I do. Each time I create a job I assign it a date. The problem with this is the days I don't have jobs aren't stored in the database therefore when I graph my data I never see the days that I had zero jobs.
My current query looks like this:
SELECT job_data_date, SUM(job_data_invoice_amount) as job_data_date_income
FROM job_data
WHERE job_data_date >= '2010-05-05'
GROUP BY job_data_date
ORDER BY job_data_date;
The output is:
| job_data_date | job_data_date_income |
| 2010-05-17 | 125 |
| 2010-05-18 | 190 |
| 2010-05-20 | 170 |
As you can see from the example output the 2010-05-19 would not show up in the results because it was never stored there.
Is there a way to show the dates that are missing?
Thank you,
Marat
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一个想法是，您可以拥有一个表，其中包含要显示的所有日期，然后与该表进行外连接。
因此，如果您有一个名为 alldates 的表，其中包含一列 (job_data_date)：
缺点是您需要在该表中填充您想要显示的所有日期。
One idea is that you could have a table with all of the dates in it that you want to show and then do an outer join with that table.
So if you had a table called alldates with one column (job_data_date):
The down side is that you would need to keep this table populated with all of the dates you want to show.
没有合理的方法可以使用纯 SQL（至少在 MySQL 上）来执行此操作，而无需创建包含每个设计日期的表。最好的选择是更改使用该查询结果的应用程序来填补漏洞本身。不要只用图形表示它收到的值，而是用一个简单的循环构造它自己的一组值；一次计算一天，并填写查询中可用的值。
There's no reasonable way to do this using pure SQL, on MySQL at least, without creating a table with every date ever devised. Your best option is to alter the application that's using the results of that query to fill in the holes itself. Rather than graphing only the values it received, construct its own set of values with a simple loop; counting up one day at a time, filling in values from the query wherever they're available.