MySQL 生成一系列日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14105018/
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
Generating a series of dates
提问by Bohemian
What is the best way in mysql to generate a series of dates in a given range?
mysql 在给定范围内生成一系列日期的最佳方法是什么？
The application I have in mind is to write a report query that returns a row for every date, regardless of whether there is any data to report. In its simplest form:
我想到的应用程序是编写一个报告查询，该查询为每个日期返回一行，而不管是否有任何数据要报告。以其最简单的形式：
select dates.date, sum(sales.amount)
from <series of dates between X and Y> dates
left join sales on date(sales.created) = dates.date
group by 1
I have tried creating a table with lots of dates, but that seems like a poor workaround.
我曾尝试创建一个包含很多日期的表格，但这似乎是一个糟糕的解决方法。
采纳答案by BellevueBob
I think having a calendar tableis a good idea; you can gain a lot of reporting and query functionality, especially when filling sparse data ranges.
我认为有一个日历表是个好主意；您可以获得大量报告和查询功能，尤其是在填充稀疏数据范围时。
I found this articlewith what seems to be a good example.
我发现这篇文章似乎是一个很好的例子。
回答by computingfreak
if you're in a situation like me where creating temporary tables is prohibited, and setting variables is also not allowed, but you want to generate a list of dates in a specific period, say current year to do some aggregation, use this
如果你像我这样的情况禁止创建临时表，也不允许设置变量，但你想生成一个特定时期的日期列表，比如当年做一些聚合，使用这个
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2017-01-01' and '2017-12-31'
回答by bonCodigo
You may use a variable generate date series:
您可以使用变量生成日期系列：
Set @i:=0;
SELECT DATE(DATE_ADD(X,
INTERVAL @i:=@i+1 DAY) ) AS datesSeries
FROM yourtable, (SELECT @i:=0) r
where @i < DATEDIFF(now(), date Y)
;
Not sure if this is what you have tried :) though.
不确定这是否是您尝试过的:) 不过。
Next use above generated query as a table to left join:
接下来使用上面生成的查询作为表left join：
set @i:=0;
select
d.dates,
sum(s.amount) as TotalAmount
from(
SELECT DATE(DATE_ADD(X,
INTERVAL @i:=@i+1 DAY) ) AS dateSeries
FROM Sales, (SELECT @i:=0) r
where @i < DATEDIFF(now(), date Y)
) dates d
left join Sales s
on Date(s.Created) = Date(d.dateSeries)
group by 1
;
回答by Saharsh Shah
You can also use Temporary Table to generate date series. Check below query:
您还可以使用临时表来生成日期系列。检查以下查询：
CREATE TEMPORARY TABLE daterange (dte DATE);
SET @counter := -1;
WHILE (@counter < DATEDIFF(DATE(_todate), DATE(_fromdate))) DO
INSERT daterange VALUES (DATE_ADD(_fromdate, INTERVAL @counter:=@counter + 1 DAY));
END WHILE;
SELECT dates.dte, SUM(sales.amount)
FROM daterange dates
LEFT JOIN sales ON DATE(sales.created) = dates.date
GROUP BY dates.dte;
