php - MySQL查找给定长度的所有周期，没有相关记录

Question

我正在用 PHP/MySQL 制作一个预订系统。

我希望能够选择一个月份，选择一个预订时长，然后找出该月内有多少个给定时长的时段可用(即，未标记为已预订)。

我首先创建了一个日期表，每一天都有一个字段指示它是否被保留，以及一个日期。这两个表是:

CREATE TABLE `day` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `booking` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

CREATE TABLE `booking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start` date DEFAULT NULL,
  `end` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

到目前为止，我已经能够计算出一个月中有多少天没有被预订——但这并不一定告诉我是否连续有 7 天:

SELECT  
    EXTRACT(YEAR FROM date) as year,
    EXTRACT( MONTH FROM date) as month,
    count(day.id)
FROM
    day
WHERE booking IS NULL
GROUP BY year, month

此时我正在考虑加载给定月份的所有日期，并在 PHP 中遍历它们以组成我的数字，但这似乎有点愚蠢且效率低下。有没有人建议我如何直接从 MySQL 中找到这些信息？

Answer 1

假设您想要 2012 年 7 月至少 7 天的范围，那么:

SELECT
  DATE_FORMAT(start, '%Y-%m-%d') start,
  MAX(days) AS days,
  DATE_FORMAT(start + INTERVAL MAX(days) - 1 DAY, '%Y-%m-%d') end
FROM (  
  SELECT
    d2.`date` start,
    (@days := IF(IFNULL(d2.booking, 0) = 0, @days + 1, 1)) AS days
  FROM
    (SELECT @days := 1) a,
    day d1
  LEFT JOIN  
    day d2 on d2.`date` = d1.`date` + INTERVAL 1 DAY
  WHERE
    IFNULL(d1.booking, 0) = 0
) d1
GROUP BY
  start
HAVING
  (
    EXTRACT(YEAR_MONTH FROM start) = 201207 OR
    EXTRACT(YEAR_MONTH FROM (start + INTERVAL MAX(days) - 1 DAY)) = 201207
  ) AND
  days >= 7
ORDER BY
  start

将产生这个输出:

|      START | DAYS |        END |
----------------------------------
| 2012-06-25 |   13 | 2012-07-07 |
| 2012-06-26 |   12 | 2012-07-07 |
| 2012-06-27 |   11 | 2012-07-07 |
| 2012-06-28 |   10 | 2012-07-07 |
| 2012-06-29 |    9 | 2012-07-07 |
| 2012-06-30 |    8 | 2012-07-07 |
| 2012-07-01 |    7 | 2012-07-07 |
| 2012-07-23 |    8 | 2012-07-30 |
| 2012-07-24 |    7 | 2012-07-30 |

从句

(  
    EXTRACT(YEAR_MONTH FROM start) = 201207 OR
    EXTRACT(YEAR_MONTH FROM (start + INTERVAL MAX(days) - 1 DAY)) = 201207
) 

用于包括所有范围，即使范围延伸到上个月或下个月。如果您想要只在一个月内下降的范围，请使用:

EXTRACT(YEAR_MONTH FROM start) = 201207 AND
EXTRACT(YEAR_MONTH FROM (start + INTERVAL MAX(days) - 1 DAY)) = 201207

参见 http://sqlfiddle.com/#!2/4e36a/4用于交互式演示。