現在要用到的有兩張表 room_type和room_info
一共有3個酒店，id為1~3。然后每個酒店有3種房型，id分別為1~9。
room_type里存著，每個房型的名字，id，對應的酒店id
room_info里存的是，每一天、每個房型的價格和剩余房間數量remain是多少。
想請教的題目是：
指定時間范圍和要預定的房間數量，查詢滿足條件（時間，剩余房間數量）的酒店、房型選擇及其平均價格，并按平均價格從低到高進行排序。查詢結果包含酒店，選擇的房型搭配和數量，以及最低的平均價格。
##不要求為同一房型，但必須為同一酒店，而且中途可以換房型。
##比如，選擇入住時間為2018-11-14~2018-11-15，預定房間數為5
##回傳選擇酒店A，11-14選擇id為3的房型4間，id為2的房型1間，11-15選擇id為3的房型3間，id為2的房型2間，最低價格為2000
##就用mysql實作，不能使用python、c++之類的
做了一天也沒寫出來，網上也沒看到類似的題目，所以來求助大神們~
資料如下
-- ----------------------------
-- Table structure for hotel
-- ----------------------------
DROP TABLE IF EXISTS `hotel`;
CREATE TABLE `hotel` (
`hotel_id` int(11) NOT NULL,
`hotel_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`stars` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`hotel_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of hotel
-- ----------------------------
INSERT INTO `hotel` VALUES (1, '惠民旅館', 5);
INSERT INTO `hotel` VALUES (2, '風景旅館', 4);
INSERT INTO `hotel` VALUES (3, '商務旅館', 4);
-- ----------------------------
-- Table structure for room_info
-- ----------------------------
DROP TABLE IF EXISTS `room_info`;
CREATE TABLE `room_info` (
`info_id` int(11) NOT NULL,
`date` date NULL DEFAULT NULL,
`price` decimal(10, 2) NULL DEFAULT NULL,
`remain` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`room_id` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`info_id`) USING BTREE,
INDEX `room_info_key`(`room_id`) USING BTREE,
CONSTRAINT `room_info_key` FOREIGN KEY (`room_id`) REFERENCES `room_type` (`room_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of room_info
-- ----------------------------
INSERT INTO `room_info` VALUES (1, '2018-11-14', 500.00, '5', 1);
INSERT INTO `room_info` VALUES (2, '2018-11-15', 500.00, '4', 1);
INSERT INTO `room_info` VALUES (3, '2018-11-16', 600.00, '6', 1);
INSERT INTO `room_info` VALUES (4, '2018-11-14', 300.00, '6', 2);
INSERT INTO `room_info` VALUES (5, '2018-11-15', 300.00, '5', 2);
INSERT INTO `room_info` VALUES (6, '2018-11-16', 400.00, '5', 2);
INSERT INTO `room_info` VALUES (7, '2018-11-14', 200.00, '4', 3);
INSERT INTO `room_info` VALUES (8, '2018-11-15', 200.00, '3', 3);
INSERT INTO `room_info` VALUES (9, '2018-11-16', 300.00, '4', 3);
INSERT INTO `room_info` VALUES (10, '2018-11-14', 450.00, '5', 4);
INSERT INTO `room_info` VALUES (11, '2018-11-15', 300.00, '5', 4);
INSERT INTO `room_info` VALUES (12, '2018-11-16', 450.00, '5', 4);
INSERT INTO `room_info` VALUES (13, '2018-11-14', 400.00, '2', 5);
INSERT INTO `room_info` VALUES (14, '2018-11-15', 250.00, '2', 5);
INSERT INTO `room_info` VALUES (15, '2018-11-16', 400.00, '2', 5);
INSERT INTO `room_info` VALUES (16, '2018-11-14', 300.00, '1', 6);
INSERT INTO `room_info` VALUES (17, '2018-11-15', 200.00, '1', 6);
INSERT INTO `room_info` VALUES (18, '2018-11-16', 300.00, '5', 6);
INSERT INTO `room_info` VALUES (19, '2018-11-14', 300.00, '2', 7);
INSERT INTO `room_info` VALUES (20, '2018-11-15', 250.00, '3', 7);
INSERT INTO `room_info` VALUES (21, '2018-11-16', 300.00, '8', 7);
INSERT INTO `room_info` VALUES (22, '2018-11-14', 250.00, '1', 8);
INSERT INTO `room_info` VALUES (23, '2018-11-15', 200.00, '1', 8);
INSERT INTO `room_info` VALUES (24, '2018-11-16', 200.00, '5', 8);
INSERT INTO `room_info` VALUES (25, '2018-11-14', 200.00, '2', 9);
INSERT INTO `room_info` VALUES (26, '2018-11-15', 150.00, '4', 9);
INSERT INTO `room_info` VALUES (27, '2018-11-16', 150.00, '4', 9);
-- ----------------------------
-- Table structure for room_type
-- ----------------------------
DROP TABLE IF EXISTS `room_type`;
CREATE TABLE `room_type` (
`room_id` int(11) NOT NULL,
`room_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`hotel_id` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`room_id`) USING BTREE,
INDEX `hotel_room_key`(`hotel_id`) USING BTREE,
CONSTRAINT `hotel_room_key` FOREIGN KEY (`hotel_id`) REFERENCES `hotel` (`hotel_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of room_type
-- ----------------------------
INSERT INTO `room_type` VALUES (1, '大床房', 1);
INSERT INTO `room_type` VALUES (2, '雙人房', 1);
INSERT INTO `room_type` VALUES (3, '三人房', 1);
INSERT INTO `room_type` VALUES (4, '海景房', 2);
INSERT INTO `room_type` VALUES (5, '園景房', 2);
INSERT INTO `room_type` VALUES (6, '山景房', 2);
INSERT INTO `room_type` VALUES (7, '總統套房', 3);
INSERT INTO `room_type` VALUES (8, '豪華套房', 3);
INSERT INTO `room_type` VALUES (9, '33號房', 3);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/76703.html
標籤：MySQL