Этот пост был последний раз отредактирован Miaolinsen 6.12.2018 17:03
Я не делал это с mysql, я делал это с mssql:
/*
Укажите временной диапазон и количество номеров для бронирования, запросите отели, выбор типов номеров и их средние цены, соответствующие условиям (время, количество оставшихся номеров), и отсортируйте их по средней цене от самой низкой до самой высокой. Результаты включают отели, выбранные комбинации номеров и количество номеров, а также самую низкую среднюю цену.
##不要求为同一房型, но это должен быть тот же отель, и вы можете изменить тип номера на середине. -- Разбор: Это значит, что нет необходимости учитывать тип комнаты, учитывается только количество комнат
##比如, выберите время регистрации 2018-11-14~2018-11-15, и количество забронированных номеров составляет 5 — Анализ: отель должен находиться в диапазоне дат, и существует спрос на количество номеров каждый день (если в определённый день номера нет, это не соответствует стандарту).
##返回选择酒店A, 11-14 выберите 4 типа комнат с ID 3, 1 тип комнаты с ID 2, 11-15 выберите 3 типа комнат с ID 3, 2 типа комнат с ID 2, а минимальная цена — 2000 — Анализ: Рассчитать среднюю цену по минимальной конфигурации за день
##老师要求只能用sql语句, реализация mysql, не может использовать python, c++ и т.д. */
-- Входящие параметры, диапазон дат, количество требуемых комнат
ОБЪЯВЛЕНИЕ @sdate ДАТОЙ = '2018-11-14';
ОБЪЯВИТЬ @edate ДАТУ = '2018-11-15';
ОБЪЯВИТЬ @roomnum INT = 5;
-- Рассчитать количество дней пребывания
DECLARE @days INT;
SET @days = DATEDIFF(DAY, @sdate, @edate)+1;
-- Номер отеля, соответствующий диапазону дат, количество номеров
IF EXISTS(выберите * из tempdb.dbo.sysobjects, где id = object_id(N'tempdb.. ##temp_hotel') и type='U')
НАЧАЛО
Стол выброса ##temp_hotel
КОНЕЦ
ВЫБЕРИТЕ ta.hotel_id в ##temp_hotel ИЗ
(SELECT ri. [date], rt.hotel_id, SUM(ri.remain) room_num ОТ room_info ri LEFT JOIN room_type rt на ri.room_id=rt.room_id где @sdate <= [дата] и [дата] <= @edate ГРУППА BY ri. [дата],rt.hotel_id ИМЕЯ SUM(remain) >= 5) ta
ГРУППИРОВАТЬ ПО ta.hotel_id ИМЕТЬ COUNT(*) = @days
-- Рассчитывается в зависимости от отеля (настройка минимального типа номера на день)
IF EXISTS(выберите * из tempdb.dbo.sysobjects, где id = object_id(N'tempdb.. ##temp_result') и type='U')
НАЧАЛО
Таблица сброса ##temp_result
КОНЕЦ
-- Предварительная таблица результатов Дата отеля Тип номера Идентификация Тип номера Название Количество номеров Цена номера Отель Дневное общее количество отелей Дневная средняя цена отеля Общая цена отеля Общая цена отеля Общая дневная цена номера
СОЗДАТЬ ТАБЛИЦУ ##temp_result(
hotel_id ИНТЕЛЛЕКТ,
hotel_name вархар (255),
[дата] ДАТА,
room_id ИНТЕЛЛЕКТ,
room_name ВАРШАР (255),
room_num ИНТЕЛЛЕКТ,
ДЕСЯТИЧНАЯ ЦЕНА комнаты,
dsumprice DECIMAL,
davgprice DECIMAL,
sumpric DECIMAL,
sumavgpric DECIMAL
)
ОБЪЯВИТЬ @hid ИНТЕЛЛЕКТ; -- Удостоверение отеля
DECLARE @infoid INT; -- info id
--утверждать, что курсор — это коллекция
DECLARE my_cursor курсор для (SELECT hotel_id из ##temp_hotel);
--Откройте курсор--
Открыть my_cursor
--Переменные курсора цикла запуска--
Принесите дальше из my_cursor в @hid
в то время как @@FETCH_STATUS = 0 -- возвращает состояние последнего курсора, выполненного оператором FETCH --
НАЧАЛО
-- По номеру отеля указана дата повторяющегося появления
IF EXISTS(выберите * из tempdb.dbo.sysobjects, где id = object_id(N'tempdb.. ##temp_room') и type='U')
НАЧАЛО
Таблица сбросов ##temp_room
КОНЕЦ
-- Отели, по дате, с номерами, отсортированными по дате. Ставка
ВЫБЕРИТЕ info_id, [дата], цену, остаться, room_id В ##temp_room ИЗ dbo.room_info, ГДЕ @sdate <= [дата] и [дата] <= @edate И ОСТАЮТСЯ > 0 И room_id В (ВЫБЕРИТЕ rt.room_id ИЗ dbo.room_type rt ГДЕ rt.hotel_id=@hid) ЗАКАЗ ПО [дате] ASC, цене ASC
DECLARE @thisrdate DATE = NULL; -- Свидание
ОБЪЯВИТЬ @thisrnum INT=0; -- Количество выбранных номеров на текущий день
DECLARE @rdate DATE = NULL; -- Свидание
ОБЪЯВИТЬ @rmnum INT = 0; -- Количество комнат по типу комнат
-- Цикл по дате
ОБЪЯВИТЬ @my_cursor2 КУРСОР;
set @my_cursor2 = КУРСОР ДЛЯ SELECT info_id Из ##temp_room ORDER BY [DATE] ASC, цена ASC
ОТКРЫТЫЙ @my_cursor2;
ПРИНЕСИ ДАЛЬШЕ ИЗ @my_cursor2 В @infoid
WHILE(@@FETCH_STATUS=0)
НАЧАЛО
--##temp_result
ВЫБЕРИТЕ @rmnum=остаться, @rdate=[дата] Из ##temp_room ГДЕ info_id=@infoid
ЕСЛИ (@thisrdate РАВНО НУЛЮ ИЛИ @thisrdate != @rdate)
НАЧАЛО
ПЕЧАТНАЯ @rdate
SET @thisrdate = @rdate;
НАБОР @thisrnum = 0;
КОНЕЦ
-- Тип комнаты для выбора дозировочных форм
IF(@thisrnum <= @roomnum)
НАЧАЛО
IF (@thisrnum + @rmnum > @roomnum)
НАЧАЛО
-- Количество комнат, которые нужно использовать
SET @rmnum = @roomnum-@thisrnum;
КОНЕЦ
КОНЕЦ
ДРУГОЕ
НАЧАЛО
-- Для этой даты не требуется место
SET @rmnum = 0;
КОНЕЦ
IF (@rmnum > 0)
НАЧАЛО
-- Добавлять результаты по заданному числу
ВСТАВЬТЕ В ##temp_result(hotel_id, hotel_name, [дата], room_id, room_name, room_num, roomprice, dsumprice, davgprice, sumpric, sumavgpric)
ВЫБЕРИТЕ @hid, '', @thisrdate, room_id, '', @rmnum, price, 0, 0, 0 ИЗ ##temp_room ГДЕ info_id = @infoid
-- Количество комнат достигло рассчитанной средней цены
SET @thisrnum = @thisrnum + @rmnum;
IF (@thisrnum = @roomnum)
НАЧАЛО
-- Общая цена по отелям, рассчитана на даты
ОБНОВЛЕНИЕ ##temp_result SET dsumprice=(ВЫБЕРИТЕ SUM(room_num*roomprice) ИЗ ##temp_result tr, ГДЕ tr.hotel_id=@hid И TR.[ DATE]=@thisrdate) ГДЕ hotel_id=@hid А [дата]=@thisrdate
-- Рассчитать среднюю цену номера по отелям, общую цену по дате
ОБНОВЛЕНИЕ ##temp_result SET davgprice=(dsumprice/@roomnum) ГДЕ hotel_id=@hid А [date]=@thisrdate
КОНЕЦ
КОНЕЦ
ПРИНЕСИ ДАЛЬШЕ ИЗ @my_cursor2 В @infoid;
КОНЕЦ
близко @my_cursor2; --Выключи курсор
deallocate @my_cursor2; --выпуск курсора
-- По отелям, рассчитайте общую цену отеля
ОБНОВЛЕНИЕ ##temp_result SET sumpric=(ВЫБЕРИТЕ SUM(room_num*roomprice) ИЗ ##temp_result tr, ГДЕ tr.hotel_id=@hid) ГДЕ hotel_id=@hid
-- Рассчитать среднюю дневную цену всего номера в отеле
ОБНОВЛЕНИЕ ##temp_result УСТАНОВИТЬ sumavgpric=(sumpric/@roomnum/@days) ГДЕ hotel_id=@hid
FETCH далее из my_cursor в @hid — переходите к следующему курсору, без мертвых петель
КОНЕЦ
Закрыть my_cursor -- закрыть курсор
deallocate my_cursor — отпустить курсор
-- Добавьте название отеля и тип комнаты в временный стол
ОБНОВЛЕНИЕ ##temp_result SET hotel_name=(ВЫБЕРИТЕ ht.hotel_name ИЗ DBO.HOTEL HT, ГДЕ ht.hotel_id=##temp_result.hotel_id),
room_name=(ВЫБРАТЬ rt.room_name ИЗ dbo.room_type RT, ГДЕ rt.room_id=##temp_result.room_id)
-- Проверьте результаты из таблицы результатов
ВЫБЕРИТЕ * Из ##temp_result
-- Завершение уборки временных столов
IF EXISTS(выберите * из tempdb.dbo.sysobjects, где id = object_id(N'tempdb.. ##temp_hotel') и type='U')
НАЧАЛО
Стол выброса ##temp_hotel
КОНЕЦ
IF EXISTS(выберите * из tempdb.dbo.sysobjects, где id = object_id(N'tempdb.. ##temp_room') и type='U')
НАЧАЛО
Таблица сбросов ##temp_room
КОНЕЦ
IF EXISTS(выберите * из tempdb.dbo.sysobjects, где id = object_id(N'tempdb.. ##temp_result') и type='U')
НАЧАЛО
Таблица сброса ##temp_result
КОНЕЦ
|
Опубликовано 05.12.2018 21:32:38
|
|
|
|
Хозяин|
Опубликовано 06.12.2018 14:11:48
|
Опубликовано 05.12.2018 21:57:13
|
Опубликовано 06.12.2018 7:56:39
|
