mysql - MySQL中选定的日期间隔

Question

我正在使用 MySQL。我正在尝试获取属于一组日期间隔中每个间隔的预订之夜。但是有些间隔比其他间隔更受欢迎，因此我会尽可能多地度过首选间隔的夜晚，并用**不喜欢的间隔**来填补空白。为了说明这一点，我将在此处展示:

给定日期:

入住 => 2016-01-16

查看 => 2016-02-08

总夜数 => 24

      Preferred |  date_from   |  date_to   | Nights     
----------------------------------------------------
     1          |  2016-01-15  | 2016-01-17 | 2
     1          |  2016-02-03  | 2016-02-10 | 6
     1          |  2016-01-20  | 2016-01-25 | 6
     0          |  2016-01-20  | 2016-01-31 | 2 (2016-01-26 and 2016-01-31 because the other nights are covered by a preferred period)
     1          |  2016-01-27  | 2016-01-30 | 4
     0          |  2016-01-15  | 2016-01-17 | 0 (these dates are covered by a the first interval which is a preferred interval )
     0          |  2016-02-01  | 2016-02-10 | 2 (just 2016-02-01 and 2016-02-02 because 03 - 08 are covered by the second interval which is a preferred interval)
     0          |  2016-01-18  | 2016-01-19 | 2

如何在 MySQL 中实现这一点？

Answer 1

假设您有一个包含 Preferred、date_from、date_to 列的表，并且您只是想计算入住天数。

你可以试试这个查询。

SET @checkin = '2016-01-16';
SET @checkout = '2016-02-08';

SELECT T0.preferred,T0.date_from,T0.date_to,IFNULL(NIGHTS.nights,0) as Nights
FROM YourTable T0
LEFT JOIN
    (SELECT T1.preferred,T1.date_from,T1.date_to,COUNT(*) AS Nights
       FROM YourTable AS T1
       INNER JOIN
        (SELECT (@checkin + INTERVAL n DAY) as singleday
         FROM numbers 
         WHERE (@checkin + INTERVAL n DAY) <= @checkout)DAYS1
      ON DAYS1.singleday BETWEEN T1.date_from AND T1.date_to
      WHERE T1.preferred = 1
      OR NOT EXISTS 
      (SELECT 1
       FROM YourTable AS T
       WHERE T.preferred = 1
         AND DAYS1.singleday BETWEEN T.date_from AND T.date_to
       )
    GROUP BY T1.preferred,T1.date_from,T1.date_to
    )NIGHTS
ON T0.preferred = NIGHTS.preferred
AND T0.date_from = NIGHTS.date_from
AND T0.date_to = NIGHTS.date_to
WHERE
    T0.date_from <= @checkout
AND T0.date_to >= @checkin
;

http://sqlfiddle.com/#!9/d64344/10您可以将 @checkout 和 @checkin 替换为您的实际 checkin 和 checkout 时间。并且您可以将出现的 YourTable 替换为您的实际表名

哦，是的，在 sqlfiddle 中我包含了一个名为 Numbers 的表，其中包含 n 列的数字，从 0 向上计数到任何可能的最大停留天数。您还需要创建此表。

创建表号使用下面

CREATE TABLE numbers AS
SELECT a.n+b.n+c.n+d.n+e.n+f.n+g.n+h.n+i.n as n
FROM
(SELECT 0 as n UNION SELECT 1)a,
(SELECT 0 as n UNION SELECT 2)b,
(SELECT 0 as n UNION SELECT 4)c,
(SELECT 0 as n UNION SELECT 8)d,
(SELECT 0 as n UNION SELECT 16)e,
(SELECT 0 as n UNION SELECT 32)f,
(SELECT 0 as n UNION SELECT 64)g,
(SELECT 0 as n UNION SELECT 128)h,
(SELECT 0 as n UNION SELECT 256)i;

• 查询的解释

1) 子查询 DAYS1 返回所有单一日期 从@checkin 到@checkout 范围

2) T1 加入 DAYS1 WHERE preferred 是 1 或不存在涵盖的首选行 DAYS1 的日期

3) 然后我们进行 COUNT(*) GROUP BY preferred,date_from,date_to 获取单日计数

4) 然后我们称我们的结果为 NIGHTS

5) 然后将 T0 与 NIGHTS 左连接以获得具有 0 晚的偶数行

6) 并且只返回截获@checkin/@checkout 范围的 T0 行。

更新 如果您的表太大，您可以像这样尝试缩小您的子查询范围，只包含您感兴趣的行

SET @checkin = '2016-01-16';
SET @checkout = '2016-02-08';

SELECT T0.preferred,T0.date_from,T0.date_to,IFNULL(NIGHTS.nights,0) as Nights
FROM (SELECT * FROM YourTable WHERE date_from <= @checkout AND date_to >= @checkin) T0
LEFT JOIN
    (SELECT T1.preferred,T1.date_from,T1.date_to,COUNT(*) AS Nights
       FROM (SELECT * FROM YourTable WHERE date_from <= @checkout AND date_to >= @checkin) AS T1
       INNER JOIN
        (SELECT (@checkin + INTERVAL n DAY) as singleday
         FROM numbers 
         WHERE (@checkin + INTERVAL n DAY) <= @checkout)DAYS1
      ON DAYS1.singleday BETWEEN T1.date_from AND T1.date_to
      WHERE T1.preferred = 1
      OR NOT EXISTS 
      (SELECT 1
       FROM (SELECT * FROM YourTable WHERE date_from <= @checkout AND date_to >= @checkin) AS T
       WHERE T.preferred = 1
         AND DAYS1.singleday BETWEEN T.date_from AND T.date_to
       )
    GROUP BY T1.preferred,T1.date_from,T1.date_to
    )NIGHTS
ON T0.preferred = NIGHTS.preferred
AND T0.date_from = NIGHTS.date_from
AND T0.date_to = NIGHTS.date_to
;