mysql 事件每月31日,MySQL-获取发生事件的月份列表

I am working on a PHP application that displays events. It generates a select box that allows visitors to choose a month for which events should be displayed.

My problem is in regard to the select box, which should display not only months on which an event starts or ends, but also months during which an event is still taking place (even if it started months before and ends months later).

Here is what I've got at the moment, it's pretty basic and only returns months on which events start.

SELECT

DISTINCT DATE_FORMAT(start,'%M %Y') as prettydate,

DATE_FORMAT(start,'%m%Y') as monthyear

FROM

`events`

WHERE

start >= NOW()

ORDER BY

start ASC;

How can I also get February & July but also March, April, May & June 2014 in the result if the only event I've got in 2014 starts in February and finishes in July for example?

Thanks in advance for your help!

解决方案

Assuming you don't have any events that span more than 1 year, and using the following start/end dates:

('2012-01-01','2012-01-31'),

('2012-03-01','2012-03-31'),

('2012-05-01','2012-06-01'),

('2012-07-01','2012-07-31'),

('2012-09-01','2012-10-31'),

('2012-11-01','2012-11-30');

this query:

SELECT DISTINCT

DISTINCT DATE_FORMAT(e.start + INTERVAL ids.id MONTH,'%M %Y') as prettydate,

DATE_FORMAT(e.start + INTERVAL ids.id MONTH,'%m%Y') as monthyear

FROM

(

SELECT 0 AS id

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

UNION SELECT 10

UNION SELECT 11

) ids,

events e

WHERE

EXTRACT(YEAR_MONTH FROM e.start + INTERVAL ids.id MONTH) <=

EXTRACT(YEAR_MONTH FROM e.end)

ORDER BY

e.start + INTERVAL ids.id MONTH

will return:

prettydate monthyear

January 2012 012012

March 2012 032012

May 2012 052012

June 2012 062012

July 2012 072012

September 2012 092012

October 2012 102012

November 2012 112012