架构师_程序员

 找回密码
 注册[Register]

QQ登录

只需一步,快速开始

查看: 147|回复: 11

[新问题] 如何选择价格最低的n间房

[复制链接]
发表于 2018-12-5 21:32:38 | 显示全部楼层 |阅读模式
现在要用到的有两张表 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
##老师要求只能用sql语句,mysql实现,不能使用python、c++之类的
做了一天也没写出来,网上也没看到类似的题目,所以来求助大神们,想请教下大家会怎么实现,感谢~

  1. -- ----------------------------

  2. -- Table structure for hotel

  3. -- ----------------------------

  4. DROP TABLE IF EXISTS `hotel`;

  5. CREATE TABLE `hotel`  (

  6.   `hotel_id` int(11) NOT NULL,

  7.   `hotel_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  8.   `stars` int(11) NULL DEFAULT NULL,

  9.   PRIMARY KEY (`hotel_id`) USING BTREE

  10. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;



  11. -- ----------------------------

  12. -- Records of hotel

  13. -- ----------------------------

  14. INSERT INTO `hotel` VALUES (1, '惠民旅馆', 5);

  15. INSERT INTO `hotel` VALUES (2, '风景旅馆', 4);

  16. INSERT INTO `hotel` VALUES (3, '商务旅馆', 4);

  17. -- ----------------------------

  18. -- Table structure for room_info

  19. -- ----------------------------

  20. DROP TABLE IF EXISTS `room_info`;

  21. CREATE TABLE `room_info`  (

  22.   `info_id` int(11) NOT NULL,

  23.   `date` date NULL DEFAULT NULL,

  24.   `price` decimal(10, 2) NULL DEFAULT NULL,

  25.   `remain` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  26.   `room_id` int(11) NULL DEFAULT NULL,

  27.   PRIMARY KEY (`info_id`) USING BTREE,

  28.   INDEX `room_info_key`(`room_id`) USING BTREE,

  29.   CONSTRAINT `room_info_key` FOREIGN KEY (`room_id`) REFERENCES `room_type` (`room_id`) ON DELETE RESTRICT ON UPDATE RESTRICT

  30. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;



  31. -- ----------------------------

  32. -- Records of room_info

  33. -- ----------------------------

  34. INSERT INTO `room_info` VALUES (1, '2018-11-14', 500.00, '5', 1);

  35. INSERT INTO `room_info` VALUES (2, '2018-11-15', 500.00, '4', 1);

  36. INSERT INTO `room_info` VALUES (3, '2018-11-16', 600.00, '6', 1);

  37. INSERT INTO `room_info` VALUES (4, '2018-11-14', 300.00, '6', 2);

  38. INSERT INTO `room_info` VALUES (5, '2018-11-15', 300.00, '5', 2);

  39. INSERT INTO `room_info` VALUES (6, '2018-11-16', 400.00, '5', 2);

  40. INSERT INTO `room_info` VALUES (7, '2018-11-14', 200.00, '4', 3);

  41. INSERT INTO `room_info` VALUES (8, '2018-11-15', 200.00, '3', 3);

  42. INSERT INTO `room_info` VALUES (9, '2018-11-16', 300.00, '4', 3);

  43. INSERT INTO `room_info` VALUES (10, '2018-11-14', 450.00, '5', 4);

  44. INSERT INTO `room_info` VALUES (11, '2018-11-15', 300.00, '5', 4);

  45. INSERT INTO `room_info` VALUES (12, '2018-11-16', 450.00, '5', 4);

  46. INSERT INTO `room_info` VALUES (13, '2018-11-14', 400.00, '2', 5);

  47. INSERT INTO `room_info` VALUES (14, '2018-11-15', 250.00, '2', 5);

  48. INSERT INTO `room_info` VALUES (15, '2018-11-16', 400.00, '2', 5);

  49. INSERT INTO `room_info` VALUES (16, '2018-11-14', 300.00, '1', 6);

  50. INSERT INTO `room_info` VALUES (17, '2018-11-15', 200.00, '1', 6);

  51. INSERT INTO `room_info` VALUES (18, '2018-11-16', 300.00, '5', 6);

  52. INSERT INTO `room_info` VALUES (19, '2018-11-14', 300.00, '2', 7);

  53. INSERT INTO `room_info` VALUES (20, '2018-11-15', 250.00, '3', 7);

  54. INSERT INTO `room_info` VALUES (21, '2018-11-16', 300.00, '8', 7);

  55. INSERT INTO `room_info` VALUES (22, '2018-11-14', 250.00, '1', 8);

  56. INSERT INTO `room_info` VALUES (23, '2018-11-15', 200.00, '1', 8);

  57. INSERT INTO `room_info` VALUES (24, '2018-11-16', 200.00, '5', 8);

  58. INSERT INTO `room_info` VALUES (25, '2018-11-14', 200.00, '2', 9);

  59. INSERT INTO `room_info` VALUES (26, '2018-11-15', 150.00, '4', 9);

  60. INSERT INTO `room_info` VALUES (27, '2018-11-16', 150.00, '4', 9);



  61. -- ----------------------------

  62. -- Table structure for room_type

  63. -- ----------------------------

  64. DROP TABLE IF EXISTS `room_type`;

  65. CREATE TABLE `room_type`  (

  66.   `room_id` int(11) NOT NULL,

  67.   `room_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  68.   `hotel_id` int(11) NULL DEFAULT NULL,

  69.   PRIMARY KEY (`room_id`) USING BTREE,

  70.   INDEX `hotel_room_key`(`hotel_id`) USING BTREE,

  71.   CONSTRAINT `hotel_room_key` FOREIGN KEY (`hotel_id`) REFERENCES `hotel` (`hotel_id`) ON DELETE RESTRICT ON UPDATE RESTRICT

  72. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;



  73. -- ----------------------------

  74. -- Records of room_type

  75. -- ----------------------------

  76. INSERT INTO `room_type` VALUES (1, '大床房', 1);

  77. INSERT INTO `room_type` VALUES (2, '双人房', 1);

  78. INSERT INTO `room_type` VALUES (3, '三人房', 1);

  79. INSERT INTO `room_type` VALUES (4, '海景房', 2);

  80. INSERT INTO `room_type` VALUES (5, '园景房', 2);

  81. INSERT INTO `room_type` VALUES (6, '山景房', 2);

  82. INSERT INTO `room_type` VALUES (7, '总统套房', 3);

  83. INSERT INTO `room_type` VALUES (8, '豪华套房', 3);

  84. INSERT INTO `room_type` VALUES (9, '33号房', 3);
复制代码






上一篇:PHP程序猿的自述心得分享
下一篇:价值人民币2232元的0-N2日语入门至中级签约通关名师班
码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
发表于 2018-12-6 17:00:58 | 显示全部楼层
本帖最后由 淼淋森 于 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

评分

参与人数 1MB +1 贡献 +1 收起 理由
admin + 1 + 1 热心帮助他人!

查看全部评分

码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
 楼主| 发表于 2018-12-6 14:11:48 | 显示全部楼层
xzxmustwin 发表于 2018-12-6 11:58
说实话 这个表结构设计的有点不合理

emmmm数据库老师布置的作业题,可能确实有不合理的地方,我学得不是很好就没看出来。。。
不过,关键还是解题思路嘛,想问问朋友你有啥建议不?
码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
 楼主| 发表于 2018-12-6 14:09:20 | 显示全部楼层
lzzsf 发表于 2018-12-6 09:07
mysql过时了,没有客户端,给我sql server的脚本

不好意思哈,我还没用过sql sever,可能因为只是教学,所以老师要求我们用mysql来做。如果朋友愿意说说思路也十分感谢。
码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
发表于 2018-12-5 21:57:13 | 显示全部楼层
我去qq群悬赏10元,看看有人帮你回答不
码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
发表于 2018-12-6 07:56:39 | 显示全部楼层
帮顶
码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
回复

使用道具 举报

发表于 2018-12-6 09:07:08 | 显示全部楼层
mysql过时了,没有客户端,给我sql server的脚本

评分

参与人数 1MB +1 贡献 +1 收起 理由
admin + 1 + 1 热心!

查看全部评分

码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
发表于 2018-12-6 11:24:25 | 显示全部楼层
lzzsf 发表于 2018-12-6 09:07
mysql过时了,没有客户端,给我sql server的脚本

建议说下思路 让楼主作为参考,应该也可以
码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
发表于 2018-12-6 11:58:37 | 显示全部楼层
说实话 这个表结构设计的有点不合理

评分

参与人数 1MB +1 贡献 +1 收起 理由
admin + 1 + 1 热心!

查看全部评分

码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
 楼主| 发表于 2018-12-6 14:07:29 | 显示全部楼层
小渣渣 发表于 2018-12-5 21:57
我去qq群悬赏10元,看看有人帮你回答不

感谢,如果有人愿意回答的话,我把红包发给你吧:)
码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
您需要登录后才可以回帖 登录 | 注册[Register]

本版积分规则

免责声明:
码农网所发布的一切软件、编程资料或者文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。

Mail To:help@itsvse.com

QQ|Archiver|手机版|小黑屋|架构师 ( 鲁ICP备14021824号-2 )|网站地图

GMT+8, 2018-12-14 04:58

Powered by Discuz! X3.4

© 2001-2014 Comsenz Inc.

快速回复 返回顶部 返回列表