This post was last edited by Miaolinsen on 2018-12-6 17:03
I didn't do it with mysql, I did it with mssql:
/*
Specify the time range and the number of rooms to be booked, query the hotels, room type selections and their average prices that meet the conditions (time, number of rooms remaining), and sort them by average price from lowest to highest. The results include hotels, selected room combinations and quantities, and the lowest average price.
##不要求为同一房型, but it must be the same hotel, and you can change the room type midway. -- Parsing: It means that there is no need to consider the room type, only the number of rooms is considered
##比如, select the check-in time 2018-11-14~2018-11-15, and the number of rooms booked is 5 -- Analysis: The hotel needs to be in the date range, and there is a demand for the number of rooms every day (if there is no one on a certain day, it will not meet the standard)
##返回选择酒店A, 11-14 select 4 room types with ID 3, 1 room type with ID 2, 11-15 select 3 room types with ID 3, 2 room types with ID 2, and the lowest price is 2000 -- Analysis: Calculate the average price according to the minimum configuration per day
##老师要求只能用sql语句, mysql implementation, cannot use python, c++, etc. */
-- Incoming parameters date range, number of rooms required
DECLARE @sdate DATE = '2018-11-14';
DECLARE @edate DATE = '2018-11-15';
DECLARE @roomnum INT = 5;
-- Calculate the number of days of stay
DECLARE @days INT;
SET @days = DATEDIFF(DAY, @sdate, @edate)+1;
-- Hotel number that meets the date range, number of rooms
IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.. ##temp_hotel') and type='U')
BEGIN
drop table ##temp_hotel
END
SELECT ta.hotel_id into ##temp_hotel FROM
(SELECT ri. [date], rt.hotel_id, SUM(ri.remain) room_num FROM room_info ri LEFT JOIN room_type rt on ri.room_id=rt.room_id where @sdate <= [date] and [date] <= @edate GROUP BY ri. [date],rt.hotel_id HAVING SUM(remain) >= 5) ta
GROUP BY ta.hotel_id HAVING COUNT(*) = @days
-- Calculated according to the hotel (configure the minimum room type per day)
IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.. ##temp_result') and type='U')
BEGIN
drop table ##temp_result
END
-- Provisional Table of Results Hotel Date Room Type ID Room Type Name Number of Rooms Room Price Hotel Daily Total Hotel Daily Average Hotel Price Hotel Total Hotel Price Hotel Total Daily Average Price of Hotel Total Daily Room Price
CREATE TABLE ##temp_result(
hotel_id INT,
hotel_name varchar(255),
[date] DATE,
room_id INT,
room_name VARCHAR(255),
room_num INT,
roomprice DECIMAL,
dsumprice DECIMAL,
davgprice DECIMAL,
sumpric DECIMAL,
sumavgpric DECIMAL
)
DECLARE @hid INT; -- Hotel ID
DECLARE @infoid INT; -- info id
--affirm that the cursor is a collection
DECLARE my_cursor cursor for (SELECT hotel_id from ##temp_hotel);
--Open cursor--
open my_cursor
--Start Loop Cursor Variables--
fetch next from my_cursor into @hid
while @@FETCH_STATUS = 0 -- returns the state of the last cursor executed by the FETCH statement --
BEGIN
-- Based on the hotel ID, the recurring date is priced
IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.. ##temp_room') and type='U')
BEGIN
drop table ##temp_room
END
-- Hotels, by date, with rooms sorted by date Rate
SELECT info_id, [date], price, remain, room_id INTO ##temp_room FROM dbo.room_info WHERE @sdate <= [date] and [date] <= @edate AND remain > 0 AND room_id IN (SELECT rt.room_id FROM dbo.room_type rt WHERE rt.hotel_id=@hid) ORDER BY [date] ASC, price ASC
DECLARE @thisrdate DATE = NULL; -- Date
DECLARE @thisrnum INT=0; -- Number of rooms selected on the current date
DECLARE @rdate DATE = NULL; -- Date
DECLARE @rmnum INT = 0; -- Number of rooms by room type
-- Cycle by date
DECLARE @my_cursor2 CURSOR;
set @my_cursor2 = CURSOR FOR SELECT info_id FROM ##temp_room ORDER BY [date] ASC, price ASC
OPEN @my_cursor2;
FETCH NEXT FROM @my_cursor2 INTO @infoid
WHILE(@@FETCH_STATUS=0)
BEGIN
--##temp_result
SELECT @rmnum=remain, @rdate=[date] FROM ##temp_room WHERE info_id=@infoid
IF (@thisrdate IS NULL OR @thisrdate != @rdate)
BEGIN
PRINT @rdate
SET @thisrdate = @rdate;
SET @thisrnum = 0;
END
-- Dosage form selection room type
IF(@thisrnum <= @roomnum)
BEGIN
IF (@thisrnum + @rmnum > @roomnum)
BEGIN
-- The number of rooms you need to use
SET @rmnum = @roomnum-@thisrnum;
END
END
ELSE
BEGIN
-- No room is required for that date
SET @rmnum = 0;
END
IF (@rmnum > 0)
BEGIN
-- Add results by specified number
INSERT INTO ##temp_result(hotel_id, hotel_name, [date], room_id, room_name, room_num, roomprice, dsumprice, davgprice, sumpric, sumavgpric)
SELECT @hid, '', @thisrdate, room_id, '', @rmnum, price, 0, 0, 0, 0 FROM ##temp_room WHERE info_id = @infoid
-- The number of rooms has reached the calculated average price
SET @thisrnum = @thisrnum + @rmnum;
IF (@thisrnum = @roomnum)
BEGIN
-- Total price by hotel, calculated for dates
UPDATE ##temp_result SET dsumprice=(SELECT SUM(room_num*roomprice) FROM ##temp_result tr WHERE tr.hotel_id=@hid AND tr.[ date]=@thisrdate) WHERE hotel_id=@hid AND [date]=@thisrdate
-- Calculate the average room rate by hotel, total price by date
UPDATE ##temp_result SET davgprice=(dsumprice/@roomnum) WHERE hotel_id=@hid AND [date]=@thisrdate
END
END
FETCH NEXT FROM @my_cursor2 INTO @infoid;
END
close @my_cursor2; --Turn off the cursor
deallocate @my_cursor2; --release cursor
-- By hotel, calculate the total hotel price
UPDATE ##temp_result SET sumpric=(SELECT SUM(room_num*roomprice) FROM ##temp_result tr WHERE tr.hotel_id=@hid) WHERE hotel_id=@hid
-- Calculate the average daily price of the total room in the hotel
UPDATE ##temp_result SET sumavgpric=(sumpric/@roomnum/@days) WHERE hotel_id=@hid
FETCH next from my_cursor into @hid - go to the next cursor, no deadloops
END
close my_cursor -- close the cursor
deallocate my_cursor - release the cursor
-- Add the hotel name and room type name to the temporary table
UPDATE ##temp_result SET hotel_name=(SELECT ht.hotel_name FROM dbo.hotel ht WHERE ht.hotel_id=##temp_result.hotel_id),
room_name=(SELECT rt.room_name FROM dbo.room_type rt WHERE rt.room_id=##temp_result.room_id)
-- Check the results from the results table
SELECT * FROM ##temp_result
-- End clearing temporary tables
IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.. ##temp_hotel') and type='U')
BEGIN
drop table ##temp_hotel
END
IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.. ##temp_room') and type='U')
BEGIN
drop table ##temp_room
END
IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.. ##temp_result') and type='U')
BEGIN
drop table ##temp_result
END
|
Posted on 12/5/2018 9:32:38 PM
|
|
|
|
Landlord|
Posted on 12/6/2018 2:11:48 PM
|
Posted on 12/5/2018 9:57:13 PM
|
Posted on 12/6/2018 7:56:39 AM
|
