MySQL-获取发生事件的月份列表 [英] MySQL - Get a list of months on which events are taking place

问题描述

我正在处理一个显示事件的PHP应用程序.它会生成一个选择框，允许访问者选择应该显示事件的月份.

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;

我又如何获得2月& ;;七月，还有三月，四月，五月和如果我2014年唯一的活动是在2月开始，例如在7月结束，结果是2014年6月?

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?

提前感谢您的帮助！

推荐答案

假定您没有任何事件超过1年，并且使用以下开始/结束日期:

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');

此查询:

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

将返回:

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

请参见 http://sqlfiddle.com/#!2/12b08/3 一个可行的例子.

See http://sqlfiddle.com/#!2/12b08/3 for a working example.

这篇关于MySQL-获取发生事件的月份列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！