在MySQL中创建两个日期之间的月份名称列表 [英] Creating a list of month names between two dates in MySQL

问题描述

如何创建两个日期之间所有月份名称的列表，例如一月，二月等.例如使用MySQL ..的2012-02-01到2013-03-29，而2月将生成两次，一个用于2012年，另一个用于2013年

How can i create a list of all the month names e.g January, February etc.. between two dates. e.g 2012-02-01 to 2013-03-29 with MySQL .. whereas February will be generated twice, one for the year 2012 and the other 2013

推荐答案

我想这就是您要寻找的:

I guess this is what you're looking for:

select MonthName(aDate) from (
  select @maxDate - interval (a.a + (10 * b.a) + (100 * c.a)) month as aDate from
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) a,
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) b,
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) c,
  (select @minDate := '2012-02-01', @maxDate := '2013-03-29') d
) e
where aDate between @minDate and @maxDate

---

以防万一有人找到这篇文章并且发现它有点难以理解，我在这里加一个简短的解释:

---

Just in case someone finds this post and finds it a bit harder to understand it I'm a adding a short explanation:

这是一种动态(有点丑陋)的解决方案，用于创建不需要创建表的日期范围，并且基于以下查询，该查询可为大多数应用程序生成足够的记录(10000条记录):

This is a dynamically (and a bit ugly) solution to creating a date range that does not require creating a table and is based on the following query which generates enough records for most applications (10000 records):

select aDate from (
  select @maxDate - interval (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) day aDate from
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) a, /*10 day range*/
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) b, /*100 day range*/
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) c, /*1000 day range*/
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) d, /*10000 day range*/
  (select @minDate := '2001-01-01', @maxDate := '2002-02-02') e
) f
where aDate between @minDate and @maxDate

根据日期范围的长度，可以通过删除表(d，c，b和a的顺序)来减少动态生成的结果的数量(10000天表示27天的记录，每个记录代表一天)，并且还将它们从上层公式中删除.设置@minDate和@maxDate变量将使您可以指定要过滤结果之间的日期.

Depending on the length of the date range you can reduce the amount of dynamically generated results (10000 days means over 27 years of records each representing one day) by removing tables (d, c, b and a in that order) and also removing them from the upper formula. Setting the @minDate and @maxDate variables will allow you to specify the dates between you want to filter the results.

这篇关于在MySQL中创建两个日期之间的月份名称列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！