Mysql:在两个日期之间选择所有数据

I have a mysql table with data connected to dates. Each row has data and a date, like this:

我有一个mysql表，数据连接到日期。每一行都有数据和日期，如下所示:

2009-06-25    75
2009-07-01    100
2009-07-02    120

I have a mysql query that select all data between two dates. This is the query:

我有一个mysql查询，在两个日期之间选择所有数据。这是查询:

SELECT data FROM tbl WHERE date BETWEEN date1 AND date2

在date1和date2之间的tbl中选择数据。

My problem is that I also need to get the rows between date1 and date2 even if there is no data for a day.

我的问题是，即使一天内没有数据，我也需要获取date1和date2之间的行。

So my query would miss the dates that are empty between 2009-06-25 and 2009-07-01.

因此，我的查询将会错过2009-06-25和2009-07-01之间的空日期。

Can I in some way add these dates with just 0 as data?

我能以某种方式把这些日期加上0作为数据吗?

7 个解决方案

#1

You can use a concept that is frequently referred to as 'calendar tables'. Here is a good guide on how to create calendar tables in MySql:

您可以使用一个经常被称为“日历表”的概念。下面是在MySql中创建日历表的一个很好的指南:

-- create some infrastructure
CREATE TABLE ints (i INTEGER);
INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- only works for 100 days, add more ints joins for more
SELECT cal.date, tbl.data
FROM (
    SELECT '2009-06-25' + INTERVAL a.i * 10 + b.i DAY as date
    FROM ints a JOIN ints b
    ORDER BY a.i * 10 + b.i
) cal LEFT JOIN tbl ON cal.date = tbl.date
WHERE cal.date BETWEEN '2009-06-25' AND '2009-07-01';

You might want to create table cal instead of the subselect.

您可能希望创建表cal，而不是子选择。

#2

Select *  from  emp where joindate between date1 and date2;

But this query not show proper data.

但是这个查询没有显示正确的数据。

如

1-jan-2013 to 12-jan-2013.

But it's show data

但它的显示数据

1-jan-2013 to 11-jan-2013.

#3

its very easy to handle this situation

处理这种情况很容易

You can use BETWEEN CLAUSE in combination with date_sub( now( ) , INTERVAL 30 DAY ) AND NOW( )

可以将BETWEEN子句与date_sub(now()、INTERVAL 30天)和now()结合使用

SELECT
    sc_cust_design.design_id as id,
    sc_cust_design.main_image,
    FROM
    sc_cust_design
WHERE
    sc_cust_design.publish = 1 
    AND **`datein`BETWEEN date_sub( now( ) , INTERVAL 30 DAY ) AND NOW( )**

Happy Coding :)

编码快乐:)

#4

Do you have a table that has all dates? If not, you might want to consider implementing a calendar table and left joining your data onto the calendar table.

你们有所有日期的桌子吗?如果没有，您可能需要考虑实现一个日历表，并将数据加入到日历表中。

#5

IF YOU CAN AVOID IT.. DON'T DO IT

如果你能避免……不要这样做

Databases aren't really designed for this, you are effectively trying to create data (albeit a list of dates) within a query.

数据库不是为这个设计的，您实际上是在一个查询中尝试创建数据(尽管是一个日期列表)。

For anyone who has an application layer above the DB query the simplest solution is to fill in the blank data there.

对于任何在DB查询之上有应用程序层的人来说，最简单的解决方案是在那里填写空白数据。

You'll more than likely be looping through the query results anyway and can implement something like this:

无论如何，您都很可能通过查询结果进行循环，并实现如下内容:

loop_date = start_date

while (loop_date <= end_date){

  if(loop_date in db_data) {
    output db_data for loop_date
  }
  else {
    output default_data for loop_date
  }

  loop_date = loop_date + 1 day
}

The benefits of this are reduced data transmission; simpler, easier to debug queries; and no worry of over-flowing the calendar table.

这样做的好处是减少了数据传输;更简单，更容易调试查询;不用担心日历表会溢出来。

#6

you must add 1 day to the end date, using: DATE_ADD('$end_date', INTERVAL 1 DAY)

您必须向结束日期添加1天，使用:DATE_ADD('$end_date'，间隔1天)

#7

-1

You can use as an alternate solution:

您可以使用作为替代解决方案:

SELECT * FROM TABLE_NAME WHERE `date` >= '1-jan-2013' 
OR `date` <= '12-jan-2013'

#1