mysql - 预订表中一年的占用天数

Question

我读过这篇文章question但这并没有真正帮助我。我有类似的房屋预订系统。因此，每次有人需要租房时，他只需登录并选择他想要的房子空闲的时间并预订即可。所以就我而言，我还有一个开始时间和结束时间。在我的记录中，我有每个日期的精确时间(日期时间格式)，因此我可以根据时间精度计算日期差异。这是我的数据库的示例:

CREATE TABLE IF NOT EXISTS `bookings` (
  `id_booking` int(11) NOT NULL AUTO_INCREMENT,
  `id_user_staff` int(11) NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `details` text,
  `statut` varchar(45) DEFAULT NULL,
  `id_house` int(1) NOT NULL,
  `fees` decimal(6,0) DEFAULT NULL,
  `ip_user` varchar(15) NOT NULL,
  `date_booked` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_booking`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `users` (
  `id_user` int(5) NOT NULL DEFAULT '0',
  `Last_Name` varchar(23) DEFAULT NULL,
  `First_Name` varchar(23) DEFAULT NULL,
  `Job_Title` varchar(56) DEFAULT NULL,
  `Status` varchar(19) DEFAULT NULL,
  `Company` varchar(38) DEFAULT NULL,
  `Mobile` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `house` (
  `id_house` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `details` text,
  PRIMARY KEY (`id_house`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

我现在的需要是确定房屋在一年内的占用情况，精确度基于该房屋预订中包含的天数。该百分比是基于预订天数与一年中天数的比率。我将查询什么来获取天数并试图避免类似问题(有时会计算两次)的问题？

Answer 1

您可以使用DATEDIFF，它给出两个日期之间的天数。但由于 DATEDIFF('2015-01-01', '2015-01-01') 结果为 0 天，因此您必须在结果中添加一天。

然后，总结每个房子的所有差异:

SELECT id_house, SUM(DATEDIFF(end_date, start_date) + 1) 
FROM bookings 
GROUP BY id_house

当然，在2015年1月1日至2015年1月2日以及2015年1月2日至2015年1月3日期间预订的房屋将导致三天预订，因为一天被预订两次。您可以随后减去这些重复预订:

SELECT id_house, COUNT(*) 
FROM bookings b1
WHERE (id_house, date(start_date)) IN 
( SELECT id_house, date(end_date) FROM bookings AS b2 WHERE b1.id_booking <> b2.id_booking )
ORDER BY id_house

这将为您提供同时为 start_date 和 end_date (在两个不同的预订中)的天数。

最后，我将使用如下查询，总结一下:

SELECT bookingdays.id_house, bookingdays.days, COALESCE(doublebookings.doubledays, 0), bookingdays.days - COALESCE(doublebookings.doubledays, 0) AS totaldays
FROM
(SELECT id_house, SUM(DATEDIFF(end_date, start_date) + 1) AS days
FROM bookings 
GROUP BY id_house) bookingdays
LEFT OUTER JOIN
(SELECT id_house, COUNT(*) AS doubledays
FROM bookings b1
WHERE (id_house, date(start_date)) IN 
( SELECT id_house, date(end_date) FROM bookings AS b2 WHERE b1.id_booking <> b2.id_booking )
ORDER BY id_house) doublebookings
ON bookingdays.id_house = doublebookings.id_house