通过MySQL从数据库中检索缺少的日期 [英] Retrieve missing dates from database via 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 不会显示在结果中，因为它从未存储在那里。

有没有办法显示缺少的日期？ >

谢谢，

Marat

解决方案

一个想法是，您可以拥有一个表，其中包含您要显示的所有日期，然后与该表进行外部联接。

所以如果你有一个叫做alldates的表，一列（job_data_date）：

  SELECT ad.job_data_date，SUM（job_data_invoice_amount）作为job_data_date_income 
 FROM alldates ad left外部连接job_data jd在ad.job_data_date = jd.job_data_date 
 WHERE ad.job_data_date> ='2010-05-05'
 GROUP BY ad.job_data_date 
 ORDER BY ad.job_data_date; 
  

不利的一点是，您需要将此表格填充到您想要的所有日期显示。

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

解决方案

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):

SELECT ad.job_data_date, SUM(job_data_invoice_amount) as job_data_date_income 
FROM alldates ad left outer join job_data jd on ad.job_data_date = jd.job_data_date 
WHERE ad.job_data_date >= '2010-05-05' 
GROUP BY ad.job_data_date 
ORDER BY ad.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.

这篇关于通过MySQL从数据库中检索缺少的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！