SQL请求找到一个句点是否被完全覆盖 [英] SQL request to find if a period is fully covered

问题描述

我有一组句点：

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;

我的预期愿望是最优化的方式，知道一个给定的时期是否完全被一个或多个时段。

My intended desire, is the most optimized way to know if a given period is fully covered by one or multiple periods.

例如：

1）获取 null 请求从 2013-04-29 直到 2013-05-10 ，因为没有期限从 2013-04 = 30 到 2013-05-05

1) to get null for a request from 2013-04-29 until 2013-05-10, cause no period covers from 2013-04=30 to 2013-05-05

2）从 2013-05-06 true 或任何内容） c $ c>到 2013-05-15

2) to get the period ids (or at least true or any content) for a request from 2013-05-06 to 2013-05-15

更新：主要目标是定义给定期间（从 2013-05-06 到 2013-05-15 根据示例2）可租借。数据库中的期间是可用的租赁季节，所以如果一天不包括在内，整个住宿不能被租用。

UPDATE: The main goal is to define if the given period (from 2013-05-06 to 2013-05-15 as per example 2) is rentable. The periods in database are available rental seasons, so if any of the day are not covered, the entire stay can not be rented.

推荐答案

h2> 编辑：看到这里有一个MySQL工作SQL小提琴： SQLFiddle ，这次实际上正常工作： - ）

尝试这些。底线是如果 Shortfall> 0 然后你不能预订租金。

See here for a MySQL working SQL Fiddle: SQLFiddle, that actually works properly this time :-)

Try these. The bottom line is that if Shortfall > 0 then you can't book the rental.

MSSQL - 这是我如何工作p>

MSSQL - this is how I worked it out

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 - 这是你实际想要的

MySQL - this is what you actually want

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

这篇关于SQL请求找到一个句点是否被完全覆盖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！