mysql - 计算两个日期之间的可用房间数量

Question

我有两个数据表:

• tbroom_capacity:roomtype，quantity_room，note

  CREATE TABLE `tbroom_capacity` (
      `id` int(10) NOT NULL DEFAULT '0',
      `roomtype` varchar(30) COLLATE utf8_bin DEFAULT NULL,
      `quantity_room` int(20) DEFAULT NULL,
      `note` varchar(50) COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  

数据: https://dl.dropboxusercontent.com/u/50542216/1.png

• tbbooking:guestid，roomtypeid，quantity_room_booking，startDate , 结束日期

  CREATE TABLE `tbbooking` (
      `guestid` int(20) DEFAULT NULL,
      `roomtypeid` varchar(20) COLLATE utf8_bin DEFAULT NULL,
      `quantity_room_booking` int(20) DEFAULT NULL,
      `startDate` date DEFAULT NULL,
      `endDate` date DEFAULT NULL,
      `id` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  

数据:

https://dl.dropboxusercontent.com/u/50542216/2.png

我想计算每个房型在两个日期之间的剩余房间数。

一个例子:

共有 3 种类型的 15 间客房:10 间标准房，2 间高级房，3 间贵宾房

• 客人 1:2013-05-26 到 2013-05-27 3 个标准
• 客人 2:2013-05-27 到 2013-05-28 3 个标准
• 客人3想预订2013-05-26到2013-05-28的房间

我试过这个来检索不可用房间总数:

SELECT sum(quantity_room_booking) FROM tbbooking 
where DATE_ADD(endDate,INTERVAL -1 DAY) >= '2013-05-26' 
and startDate <= '2013-05-28' GROUP BY roomtypeid

但结果是:

• 6标准
• 2 上级
• 3 名贵宾

结果应该是:

• 3标准
• 2 上级
• 3 名贵宾

Answer 1

您可以再添加一个 bool 列，如 Is booked 并使用该列值查询值

例子:

SELECT sum(quantity_room_booking) FROM tbbooking其中 DATE_ADD(endDate,INTERVAL -1 DAY) >= '2013-05-26'and startDate <= '2013-05-28' and Is booked=true GROUP BY roomtypeid

希望这对你得到房型数量有帮助