A total of three hotels, with id 1 ~ 3, then each hotel has 3 kinds, id 1 ~ 9, respectively,
Room_type deposit, the name of each room, id, the corresponding hotel id
Room_info deposit is that every day, the price of each room and how much is the rest room number remain,
Want to consult the topic is:
Specify a time range and to reserve the room number, the query to meet conditions (time, rest room number) of the hotel, room selection and average price, average price and press from low to attitude of sorting, the query results include hotels, choose room is tie-in and quantity, and the lowest average price,
# # is not required for the same room, but must be the same hotel, and can change room,
# #, for example, choose check-in time for the 2018-11-14 ~ 2018-11-14, reserve a room for 5
11-14 # # back to choose A hotel, select the id of 3 room 4, id is 2 room 1, between 11 to 15 choice between id for 3 room 3, id for 2 room between 2, the lowest price for 2000
# # with mysql implementation, cannot use python, c + +, such as
Do a day also didn't write, online didn't see a similar topic, since the turn to the great god ~
The data shown in the following
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Table structure for hotel
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
DROP TABLE IF the EXISTS ` hotel `;
The 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, 'huimin hotel, 5);
INSERT INTO ` hotel ` VALUES (2, 'scenery hotel, 4);
INSERT INTO ` hotel ` VALUES (3, 'business hotel, 4);
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Table structure for room_info
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
DROP TABLE IF the EXISTS ` room_info `;
The 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,
The INDEX ` room_info_key ` (` room_id `) USING BTREE,
The CONSTRAINT ` room_info_key ` FOREIGN KEY (` room_id `) REFERENCES ` room_type ` (` room_id `) ON DELETE RESTRICT ON the 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 the EXISTS ` room_type `;
The 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,
The INDEX ` hotel_room_key ` (` hotel_id `) USING BTREE,
The CONSTRAINT ` hotel_room_key ` FOREIGN KEY (` hotel_id `) REFERENCES ` hotel ` (` hotel_id `) ON DELETE RESTRICT ON the UPDATE RESTRICT
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Compact;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Records of room_type
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
INSERT INTO ` room_type ` VALUES (1, 'big bed room, 1);
INSERT INTO ` room_type ` VALUES (2, 'double', 1);
INSERT INTO ` room_type ` VALUES (3, 'the three rooms', 1);
nullnullnullnullnullnull