Mysql：选择两个日期之间的所有数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1080207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql: Select all data between two dates
提问by
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
SELECT data FROM tbl WHERE date BETWEEN date1 AND date2
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 作为数据吗？
回答by Josef Pfleger
You can use a concept that is frequently referred to as 'calendar tables'. Hereis 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 calinstead of the subselect.
您可能想要创建表cal而不是子选择。
回答by gulab
Select * from emp where joindate between date1 and date2;
But this query not show proper data.
但是这个查询没有显示正确的数据。
Eg
例如
1-jan-2013 to 12-jan-2013.
But it's show data
但它是显示数据
1-jan-2013 to 11-jan-2013.
回答by Arshad Munir
its very easy to handle this situation
处理这种情况很容易
You can use BETWEEN CLAUSEin combination with date_sub( now( ) , INTERVAL 30 DAY ) AND NOW( )
您可以将BETWEEN CLAUSE与date_sub( now( ) , INTERVAL 30 DAY ) AND 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 :)
快乐编码:)
回答by feihtthief
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.
你有一张包含所有日期的表吗？如果没有，您可能需要考虑实施日历表并将您的数据加入日历表。
回答by Arth
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.
对于在数据库查询之上有应用层的任何人来说，最简单的解决方案是在那里填充空白数据。
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.
这样做的好处是减少了数据传输；更简单，更容易调试查询；不用担心日历表溢出。
回答by Dedi Koswara
you must add 1 day to the end date, using: DATE_ADD('$end_date', INTERVAL 1 DAY)
您必须将 1 天添加到结束日期，使用： DATE_ADD('$end_date', INTERVAL 1 DAY)
回答by Sourabh Goyal
You can use as an alternatesolution:
您可以将其用作替代解决方案：
SELECT * FROM TABLE_NAME WHERE `date` >= '1-jan-2013'
OR `date` <= '12-jan-2013'
