php - 选择日期之间的空房情况(sql 查询)

Question

我的 SQL 查询有一些问题。我正在寻找输入表格中日期之间的可用房间。

这是表架构:

table 室

• id_room、room_name、分配
• 例如:1、vip、10

表价

• id_price、id_room、价格、日期

• 例如:1, 1, 10, 2014-05-1 | 1, 1, 20, 2014-05-2 | 1, 1, 10, 2014-05-3

餐 table 预订

• id_booking、id_room、日期
• 例如:1, 1, 2014-05-1 | 2, 1, 2014-05-1 | 3, 1, 2014-05-1

我正在寻找的是:

如果2014-5-1至2014-05-3有人预订贵宾房，房间数量为8间

然后返回空白(因为vip房间有10个分配，2014-05-1有3个预订，所以只剩下7个房间)

这是sql查询

select room_name, COUNT( SELECT FROM booking WHERE date between '$checkin' and '$checkout') as total_booking, from room where allotment <= total_boking

Answer 1

这是MySQL吗？

语法错误；另外，date 是一个保留名称。请参阅下面我修改的表格和 SQL。

SELECT
    room.room_name,
    (SELECT COUNT(*) FROM booking WHERE booking.booking_date BETWEEN '' AND '') AS     total_booking
FROM
room
WHERE room.allotment <= (SELECT COUNT(*) FROM booking WHERE booking.booking_date BETWEEN '' AND '')

这是表架构；

-- ----------------------------
-- Table structure for `booking`
-- ----------------------------
DROP TABLE IF EXISTS `booking`;
CREATE TABLE `booking` (
`id_booking` int(10) NOT NULL,
`id_room` int(10) NOT NULL,
`booking_date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ----------------------------
-- Table structure for `price`
-- ----------------------------
DROP TABLE IF EXISTS `price`;
CREATE TABLE `price` (
`id_price` int(10) NOT NULL,
`id_room` int(10) NOT NULL,
`price` int(10) NOT NULL,
`price_date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- ----------------------------
-- Table structure for `room`
-- ----------------------------
DROP TABLE IF EXISTS `room`;
CREATE TABLE `room` (
`id_room` int(10) NOT NULL,
`room_name` varchar(100) NOT NULL,
`allotment` int(10) NOT NULL,
PRIMARY KEY (`id_room`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;