在 MySQL 中等价于 generate_series()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6870499/
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
generate_series() equivalent in MySQL
提问by stighy
I need to do a query and join with all days of the year but in my db there isn't a calendar table.
After google-ing I found generate_series()in PostgreSQL. Does MySQL have anything similar?
我需要进行查询并加入一年中的所有日子，但在我的数据库中没有日历表。
在 google-ing 之后，我generate_series()在 PostgreSQL 中找到了。MySQL有没有类似的东西？
My actual table has something like:
我的实际表有类似的东西：
date qty
1-1-11 3
1-1-11 4
4-1-11 2
6-1-11 5
But my query has to return:
但我的查询必须返回：
1-1-11 7
2-1-11 0
3-1-11 0
4-1-11 2
and so on ..
回答by Karolis
This is how I do it. It creates a range of dates from 2011-01-01to 2011-12-31:
我就是这样做的。它创建了从2011-01-01到2011-12-31的日期范围：
select
date_format(
adddate('2011-1-1', @num:=@num+1),
'%Y-%m-%d'
) date
from
any_table,
(select @num:=-1) num
limit
365
-- use limit 366 for leap years if you're putting this in production
The only requirement is that the number of rows in any_tableshould be greater or equal to the size of the needed range (>= 365 rows in this example). You will most likely use this as a subquery of your whole query, so in your case any_tablecan be one of the tables you use in that query.
唯一的要求是any_table 中的行数应大于或等于所需范围的大小（在此示例中为 >= 365 行）。您很可能会将它用作整个查询的子查询，因此在您的情况下any_table可以是您在该查询中使用的表之一。
回答by davpar
Enhanced version of solution from @Karolis that ensures it works for any year (including leap years):
来自@Karolis 的增强版解决方案，确保它适用于任何年份（包括闰年）：
select date from (
select
date_format(
adddate('2011-1-1', @num:=@num+1),
'%Y-%m-%d'
) date
from
any_table,
(select @num:=-1) num
limit
366
) as dt
where year(date)=2011
回答by Carmine Tambascia
I was looking to this solution but without the "hardcoded" date, and I came-up with this one valid for the current year(helped from this answers). Please note the
我正在寻找这个解决方案，但没有“硬编码”日期，我想出了这个对当年有效的解决方案（从这个答案中得到帮助）。请注意
where year(date)=2011
is not needed as the select already filter the date. Also this way, it does not matter which table(at least as stated before the table has at least 366 rows) is been used, as date is "calculated" on runtime.
不需要，因为选择已经过滤了日期。同样这样，使用哪个表（至少如表至少有 366 行之前所述）并不重要，因为日期是在运行时“计算”的。
select date from (
select
date_format(
adddate(MAKEDATE(year(now()),1), @num:=@num+1),
'%Y-%m-%d'
) date
from
your_table,
(select @num:=-1) num
limit
366 ) as dt
