如何确认房间是否已被预订一空

Question

我目前正在开发一个预订系统。我现在遇到了一个问题，我不知道一套公寓是否已经订满了。在我的数据库中，我有一个表持有所有的公寓和他们的详细信息。我想知道所有公寓的日期，例如有4间卧室的公寓。我正在运行以下sql来返回所有4卧室公寓的预订日期。

SELECT * 
FROM  `apartment_booking` AS ab
JOIN apartment AS a ON ( a.id = apartmentId ) 
JOIN booking AS b ON ( b.id = bookingId ) 
WHERE bedrooms =  '4'
ORDER BY checkIn

sql的返回是

id  CheckIn     checkOut     userId
74  2014-04-15  2014-04-22    1
75  2014-04-15  2014-04-22    1
102 2014-06-03  2014-07-07    1
71  2014-06-16  2014-06-23    1
114 2014-07-19  2014-08-02    1
121 2014-07-20  2014-08-02    1
57  2014-07-22  2014-08-05    1
122 2014-07-28  2014-08-02    1
117 2014-08-03  2014-08-10    1

因为我在系统中有4个公寓，有4个卧室，我想知道所有4个卧室都被预订的日期。

输出的示例得到日期2014-07-28至2014-08-02已被预订，因为在该日期范围内总共有四个预订。

数据库:

CREATE TABLE `apartment` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(4) NOT NULL,
  `bedrooms` int(11) NOT NULL,
  `description` varchar(500) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;
--
-- Dumping data for table `apartment`
--
INSERT INTO `apartment` (`id`, `code`, `bedrooms`, `description`) VALUES
(1, '1c', 3, ''),
(4, '4d', 4, NULL),
(5, '5b', 2, NULL),
(10, '10c', 3, NULL),
(11, '11b', 2, NULL),
(12, '12d', 4, NULL),
(13, '13c', 3, NULL),
(14, '14a', 1, 'Yo'),
(15, '15b', 2, NULL),
(16, '16b', 2, NULL),
(17, '17d', 4, NULL),
(22, '22d', 4, NULL),

CREATE TABLE `apartment_booking` (
  `id` int(11) NOT NULL auto_increment,
  `apartmentId` int(11) NOT NULL,
  `bookingId` int(11) NOT NULL,
  `ref` varchar(50) NOT NULL,
  `pax` int(11) NOT NULL default '1',
  `remarks` varchar(500) default NULL,
  `guestFullName` varchar(30) default NULL,
  `guestCountry` varchar(2) default NULL,
  `guestFlightDetails` varchar(200) default NULL,
  PRIMARY KEY  (`id`),
  KEY `apartmentId` (`apartmentId`),
  KEY `bookingId` (`bookingId`),
  KEY `ref` (`ref`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=179 ;
--
-- Dumping data for table `apartment_booking`
--
INSERT INTO `apartment_booking` (`id`, `apartmentId`, `bookingId`, `ref`, `pax`, `remarks`, `guestFullName`, `guestCountry`, `guestFlightDetails`) VALUES
(164, 1, 140, 'Hotelbeds', 5, '', 'Andrew Robertson', 'MT', '')
(165, 21, 141, 'Hotelbeds', 6, '', 'Pipitone', 'MT', ''),
(166, 5, 142, 'maltaholidaylets', 2, '', 'holly turpin', 'MT', ''),
(167, 12, 143, 'direct003', 4, '', 'Bernard Walch', 'MT', ''),
(168, 17, 144, 'meetingpoint', 4, '', 'Edvin Modigh', 'MT', ''),
(169, 23, 145, 'direct', 3, '', 'Andrea bacchetti', 'MT', ''),
(172, 25, 148, 'direct', 5, '', 'Wimold Peters', 'MT', ''),
(173, 20, 149, '7228110687', 4, '', 'Ms. Benedetta Tombari', 'MT', ''),
(174, 23, 149, '7228110687 meetingpoint', 2, '', 'Ms. Milena Moretti', 'MT', ''),
(175, 25, 150, 'meetingpoint', 6, '', 'N Burdett', 'MT', ''),
(176, 8, 151, 'Hotelbeds', 2, '', 'tito titti', 'MT', ''),
(177, 1, 152, 'meetingpoint', 3, '', 'Stephen Mckenna', 'MT', ''),
(178, 16, 153, 'mhcs', 4, '', 'Wojclech Blaszak', 'MT', '');
-- --------------------------------------------------------
--
-- Table structure for table `booking`
--
CREATE TABLE `booking` (
  `id` int(11) NOT NULL auto_increment,
  `reference` varchar(20) NOT NULL,
  `dateTime` datetime NOT NULL,
  `checkIn` date NOT NULL,
  `checkOut` date NOT NULL,
  `userId` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `agent` (`userId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=154 ;
--
-- Dumping data for table `booking`
--
INSERT INTO `booking` (`id`, `reference`, `dateTime`, `checkIn`, `checkOut`, `userId`) VALUES
(136, 'euroresort booking.b', '2014-07-02 09:30:08', '2014-08-04', '2014-08-11', 1),
(137, '7014505534', '2014-07-02 09:32:05', '2014-07-19', '2014-07-24', 1),
(138, 'BR4277518', '2014-07-02 09:45:02', '2014-08-09', '2014-08-16', 1),
(139, '100206154', '2014-07-02 10:11:45', '2014-07-27', '2014-08-03', 1),
(140, '120-135249-95', '2014-07-02 10:13:14', '2014-07-02', '2014-07-03', 1),
(141, '120-135181-94', '2014-07-02 10:14:31', '2014-08-10', '2014-08-17', 1),
(142, '000548MHL', '2014-07-02 12:38:54', '2014-08-25', '2014-09-01', 1),
(143, 'direct003', '2014-07-02 15:48:04', '2014-08-11', '2014-08-22', 1),
(144, 'SH3049361', '2014-07-02 15:52:18', '2014-08-05', '2014-08-14', 1),
(145, 'direct009', '2014-07-03 08:27:56', '2014-07-19', '2014-07-26', 1),
(148, 'direct010', '2014-07-04 08:12:13', '2014-07-08', '2014-07-22', 1),
(149, '7228110687', '2014-07-04 13:28:16', '2014-08-10', '2014-08-16', 1),
(150, '7308310623', '2014-07-07 08:39:04', '2014-08-11', '2014-08-20', 1),
(151, '120-135677-92', '2014-07-07 08:43:06', '2014-08-22', '2014-08-29', 1),
(152, '100209964', '2014-07-07 10:59:16', '2014-08-05', '2014-08-12', 1),
(153, 'mhcs', '2014-07-07 13:07:22', '2014-08-08', '2014-08-16', 1);

Answer 1

这有点复杂。

下面的查询生成一个从0到999的数字范围，并将每个数字作为天数添加到每个预订的check - kin日期，其中结果日期小于或等于预订4间客房的公寓的checkOut日期。这应该是每个公寓每天预订一排。

然后计算每个日期的预订id的数量，并与4卧室公寓的数量进行比较(来自子查询)。HAVING子句然后丢弃预订的公寓数量与有4个房间的公寓数量不相同的日期的所有行。

SELECT aBookedDate, sub2.apartment_cnt, COUNT(id) AS all_booking_cnt
FROM
(
    SELECT booking.id, DATE_ADD(booking.checkIn, INTERVAL iCnt DAY) AS aBookedDate
    FROM
    (
        SELECT units.i + tens.i * 10 + hundreds.i * 100 AS iCnt
        FROM (SELECT 0 i 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)units
        CROSS JOIN (SELECT 0 i 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)tens
        CROSS JOIN (SELECT 0 i 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)hundreds
    ) sub0
    CROSS JOIN booking
    INNER JOIN apartment_booking ON booking.id = apartment_booking.bookingId
    INNER JOIN apartment ON apartment.id = apartment_booking.apartmentId
    WHERE DATE_ADD(booking.checkIn, INTERVAL iCnt DAY) <= booking.checkOut
    AND apartment.bedrooms = 4
) sub1
CROSS JOIN 
(
    SELECT COUNT(*) AS apartment_cnt
    FROM apartment 
    WHERE bedrooms = 4
) sub2
GROUP BY aBookedDate
HAVING all_booking_cnt = sub2.apartment_cnt

SQL fiddle for it:-

http://www.sqlfiddle.com/!2/6edbe/5

Answer 2

您需要左外连接，因此您还显示未预订的公寓

SELECT * 
FROM  `apartment_booking` AS ab
JOIN apartment AS a ON ( a.id = apartmentId ) 
JOIN booking AS b ON ( b.id = bookingId ) 
WHERE bedrooms =  '4' and userId is null
ORDER BY checkIn

用户id为空的将为空(即未预订)，因为没有预订连接到该公寓。你没有说足够的结构，所以我认为你删除预订而不是保留历史记录。如果你保留了所有的历史条目，你需要用今天的日期来检查日期。

SELECT * 
FROM  `apartment_booking` AS ab
JOIN apartment AS a ON ( a.id = apartmentId ) 
LEFT OUTER JOIN booking AS b ON ( b.id = bookingId ) 
WHERE bedrooms =  '4' and checkOut > NOW()
ORDER BY checkIn

编辑:

它看起来应该是这样的，我稍后会尝试修改它:

SELECT 
    (COUNT( 
        SELECT * 
        FROM  `apartment_booking` AS ab
        JOIN apartment AS a ON ( a.id = apartmentId ) 
        LEFT OUTER JOIN booking AS b ON ( b.id = bookingId ) 
        WHERE bedrooms =  '4' and checkIn <= <<<SOMEDATEHERE>>> and checkOut >= <<<<SOMEOTHERDATEHERE>>>>>
     ) >= 4);