在MySQL中等效于generate_series() [英] generate_series() equivalent in MySQL

问题描述

我需要进行查询并与一年中的所有日子一起加入，但是在我的数据库中没有日历表.
谷歌搜索后，我在PostgreSQL中找到了generate_series(). MySQL有类似的东西吗?

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?

我的实际桌子上有这样的东西:

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 ..

推荐答案

这就是我的方法.它会创建从 2011-01-01 到 2011-12-31 的日期范围:

This is how I do it. It creates a range of dates from 2011-01-01 to 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

唯一的要求是 any_table 中的行数应大于或等于所需范围的大小(在此示例中，> = 365行).您很可能会将其用作整个查询的子查询，因此在这种情况下， any_table 可以是该查询中使用的表之一.

The only requirement is that the number of rows in any_table should 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_table can be one of the tables you use in that query.

这篇关于在MySQL中等效于generate_series()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！