我有一组句点，例如:

CREATE TABLE `periods` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `start_at` date DEFAULT NULL,
  `end_at` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `periods` WRITE;

INSERT INTO `periods` (`id`, `start_at`, `end_at`)
VALUES
    (1,'2013-04-29','2013-04-30'),
    (2,'2013-05-05','2013-05-10'),
    (3,'2013-05-10','2013-05-15'),
    (4,'2013-05-15','2013-05-16'),
    (5,'2013-05-18','2013-05-19'),
    (6,'2013-05-19','2013-05-25');

UNLOCK TABLES;

我的期望是了解给定时间段是否完全被一个或多个时间段覆盖的最优化方式。

例如:

1) 为从 2013-04-29 到 2013-05-10 的请求获取 null，因为从2013-04=30 到 2013-05-05

2) 获取从 2013-05-06 到 2013-05- 的请求的周期 ID(或至少 true 或任何内容) 15

更新:主要目标是定义给定时间段(根据示例 2 从 2013-05-06 到 2013-05-15)是否可出租。数据库中的时间段是可用的租赁季节，所以如果一天中的任何一个时间段没有包括在内，则整个住宿都不能出租。

最佳答案

编辑:请参阅此处了解 MySQL 工作的 SQL Fiddle:SQLFiddle , 这次实际上工作正常:-)

试试这些。底线是，如果 Shortfall > 0 则您无法预订租金。

MSSQL - 我就是这样解决的

DECLARE @start DATETIME = '2013-04-29' -- this will depend on your dateformat
DECLARE @end DATETIME = '2013-05-10'
DECLARE @days INT = DATEDIFF(D,@start, @end) -- this is how many days we actually want to stay
DECLARE @unusedDays INT = 0 -- this will be the number of unused days from the rental periods in which our start and end dates fall
SELECT  @UnusedDays = DATEDIFF(D,@end,end_at) FROM PERIODS WHERE (@end > start_at AND @end <= end_at) -- how many spare days are there in the final period?
SELECT  @UnusedDays = @UnusedDays + DATEDIFF(D,start_at, @start) FROM PERIODS WHERE (@start >= start_at AND @start < end_at) -- how many spare days are there in the start period?
SELECT  @days + @UnusedDays - SUM(DATEDIFF(D,start_at,end_at)) AS Shortfall, -- total shortfall in days. Zero if we are okay to rent
        SUM(DATEDIFF(D,start_at,end_at)) AS AvailableDays, -- total number of days available in all periods covering our chosen rental period
        @days AS DesiredDays, -- number of days we want to rent
        @UnusedDays AS WastedDays -- number of wasted days (if we start or end our rental mid-period)
FROM    PERIODS 
WHERE   (@start >= start_at AND @start < end_at) -- period in which our selected rental starts
OR      (end_at < @end AND start_at > @start) -- period completely within our selected rental
OR      (@end > start_at AND @end <= end_at) -- period in which our selected rental ends

这提供了这样的输出:

-- if you have @start = '2013-05-05'
-- and @end = '2013-05-13'
-- then you get
Shortfall AvailableDays DesiredDays WastedDays

0---------10------------8-----------2---------

-- if you have @start = '2013-04-29'
-- and @end = '2013-05-10'
-- then you get
Shortfall AvailableDays DesiredDays WastedDays

5---------6-------------11----------0---------

MySQL - 这才是您真正想要的

SET @start = '2013-04-29';
SET @end = '2013-05-10';
SET @days = DATEDIFF(@end, @start); -- this is how many days we actually want to stay
SET @UnusedDays = 0; -- this will be the number of unused days from the rental periods in which our start and end dates fall
SELECT  @UnusedDays := DATEDIFF(end_at,@end) FROM PERIODS WHERE (@end > start_at AND @end <= end_at); -- how many spare days are there in the final period?
SELECT 'hello';
SELECT  @UnusedDays := @UnusedDays + DATEDIFF(@start, start_at) FROM PERIODS WHERE (@start >= start_at AND @start < end_at); -- how many spare days are there in the start period?
SELECT 'hello';
SELECT  @days + @UnusedDays - SUM(DATEDIFF(end_at, start_at)) AS Shortfall, -- total shortfall in days. Zero if we are okay to rent
        SUM(DATEDIFF(end_at, start_at)) AS AvailableDays, -- total number of days available in all periods covering our chosen rental period
        @days AS DesiredDays, -- number of days we want to rent
        @UnusedDays AS WastedDays -- number of wasted days (if we start or end our rental mid-period)
FROM    PERIODS 
WHERE   (@start >= start_at AND @start < end_at) -- period in which our selected rental starts
OR      (end_at < @end AND start_at > @start) -- period completely within our selected rental
OR      (@end > start_at AND @end <= end_at); -- period in which our selected rental ends

关于mysql - SQL请求以查找是否完全覆盖了一个时间段，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/16281793/