本帖最后由 淼淋森 于 2018-12-6 17:03 编辑
我不是用mysql做的，我用的 mssql 做的：
/*
指定时间范围和要预定的房间数量，查询满足条件（时间，剩余房间数量）的酒店、房型选择及其平均价格，并按平均价格从低到高进行排序。查询结果包含酒店，选择的房型搭配和数量，以及最低的平均价格。
##不要求为同一房型，但必须为同一酒店，而且中途可以换房型。 -- 解析：表示不用考虑房型问题，只需要考虑房间数量
##比如，选择入住时间为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 -- 解析：按每天最低配置计算平均价格
##老师要求只能用sql语句，mysql实现，不能使用python、c++之类的*/
-- 传入参数 日期范围，需求房间数
DECLARE @sdate DATE = '2018-11-14';
DECLARE @edate DATE = '2018-11-15';
DECLARE @roomnum INT = 5;
-- 计算入住天数
DECLARE @days INT;
SET @days = DATEDIFF(DAY, @sdate, @edate)+1;
-- 满足日期范围、房间数量的酒店编号
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
-- 根据酒店计算(配置每天的最低房型)
IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp_result') and type='U')
BEGIN
drop table ##temp_result
END
-- 结果临时表 酒店 日期 房型id 房型名称 房间数 房间价 酒店日总价 酒店日均价 酒店总价 酒店总房日均价
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; -- 酒店id
DECLARE @infoid INT; -- info id
--申明游标为集合
DECLARE my_cursor cursor for (SELECT hotel_id from ##temp_hotel);
--打开游标--
open my_cursor
--开始循环游标变量--
fetch next from my_cursor into @hid
while @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
BEGIN
-- 根据 酒店id,循环日期 选房 计价
IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp_room') and type='U')
BEGIN
drop table ##temp_room
END
-- 酒店、按日期、有房间 按 日期 房价 排序
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; -- 日期
DECLARE @thisrnum INT=0; -- 当前日期选房的房间数
DECLARE @rdate DATE = NULL; -- 日期
DECLARE @rmnum INT = 0; -- 房型房间数
-- 按日期循环
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
-- 剂型选房型
IF(@thisrnum <= @roomnum)
BEGIN
IF (@thisrnum + @rmnum > @roomnum)
BEGIN
-- 还需要用的房间数
SET @rmnum = @roomnum-@thisrnum;
END
END
ELSE
BEGIN
-- 该日期不需要房间了
SET @rmnum = 0;
END
IF (@rmnum > 0)
BEGIN
-- 按指定数量添加结果
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
-- 房间数量已达到 计算平均价格
SET @thisrnum = @thisrnum + @rmnum;
IF (@thisrnum = @roomnum)
BEGIN
-- 按酒店，算日期总价
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
-- 按酒店，按日期总价，算房均价
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; --关闭游标
deallocate @my_cursor2; --释放游标
-- 按酒店，算酒店总价
UPDATE ##temp_result SET sumpric=(SELECT SUM(room_num*roomprice) FROM ##temp_result tr WHERE tr.hotel_id=@hid) WHERE hotel_id=@hid
-- 算酒店总房日均价
UPDATE ##temp_result SET sumavgpric=(sumpric/@roomnum/@days) WHERE hotel_id=@hid
FETCH next from my_cursor into @hid --转到下一个游标，没有会死循环
END
close my_cursor --关闭游标
deallocate my_cursor --释放游标
-- 将酒店名称、房型名称加入临时表
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)
-- 从结果表中查出结果
SELECT * FROM ##temp_result
-- 结束清除临时表
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
|
|手机版|小黑屋|架构师
( 鲁ICP备14021824号-2 )|网站地图
发表于 2018-12-5 21:32:38
楼主
发表于 2018-12-5 21:57:13
发表于 2018-12-6 07:56:39