问题描述
我有一个数据库 (MySQL)，其中包含一个包含日期范围(如 startdate 和 enddate)和一个 rate 字段的表.日期范围意味着不同的季节(低、高等).场景是这样的，一个人入住酒店，他的逗留时间是两个季节.示例数据如下:
I have a database (MySQL) with a table containing date ranges (as startdate and enddate) and a rate field. The date range implies different seasons (low, high etc.). The scenario is such that a person checks in the hotel and his duration of stay is in two seasons. A sample data is like below:
SeasonName SartDate EndDate Rate
Low 01-01-2007 30-04-2007 100.00
High 01-05-2007 31-08-2007 150.00
Peak 01-09-2007 31-12-2007 200.00
客户的入住日期是 29-04-2007，退房日期是 03-05-2007.我需要计算每个季节的确切夜数，还要计算总金额.
The client's Check In Date is 29-04-2007 and Check Out Date is 03-05-2007. I need to calculate the exact number of nights for each season and also calculate the total amount.
IDE 是 VB6.任何帮助将不胜感激.
The IDE is VB6. Any help will be extremely appreciated.
谢谢
汤姆
感谢您的回复.我需要 SQL 来提取信息.至于日期的有效性，假设费率适用于午夜 (00:00).希望我已经澄清了.
Thanks for the response. I need the SQL to extract the information. As for the date validity, lets assume the rate applies till midnight (00:00). Hope i have clarified.
汤姆
推荐答案
在酒店工作过，写过预订系统，每小时时间是无关紧要的随着计费的进行.一切总是在晚上收费.(除非您打算经营一个按小时收费的地方！;-))入住和退房是运营方面的考虑因素.
Having worked in a hotel and written the reservation system, hourly time is irrelevant as faras billing goes. Everything is always charged by night. (Unless you plan to run a place that charges by the hour! ;-)) Check-in and check-out are operational considerations.
如果您真的想编写一个真正的预订系统，请不要使用存储过程.它违背了拥有数据库的目的.
Do not use stored procuedures if you actually want to write a real reservation system.It defeats the purpose of having a database.
另外，写出这样的日期是 2007-04-29 真的很棒，因为不是每个人都来自同一个地方，这是一个国际标准.还要注意，如果你把它变成一个字符串，它仍然会被正确排序！
Also, writing out dates like this is 2007-04-29 is really great way because not every one is from the same place and this is an international standard. Also notice, if you were to turn this into a string it will still be sorted correctly!
您需要创建一个日历表，因为 MySQL 没有内置函数来执行此操作.此过程将为您建立日期.
You need make a calandar table as MySQL does not have in built in functions to do it.This procedure will build up dates for you.
drop table if exists calendar;
create table calendar
(
date_ date primary key
);
drop procedure fill_calendar;
delimiter $$
create procedure fill_calendar(start_date date, end_date date)
begin
declare date_ date;
set date_=start_date;
while date_ < end_date do
insert into calendar values(date_);
set date_ = adddate(date_, interval 1 day);
end while;
end $$
delimiter ;
call fill_calendar('2007-1-1', '2007-12-31');
来自:http://www.ehow.com/how_7571744_mysql-calendar-tutorial.html
drop table if exists rates;
create table rates
(
season varchar(100) primary key,
start_date date references calendar(date_),
end_date date references calendar(date_),
rate float
);
insert into rates values ('Low', '2007-01-01', '2007-04-30', 100.00);
insert into rates values ('High', '2007-05-01', '2007-08-31', 150.00);
insert into rates values ('Peak', '2007-09-01', '2007-12-21', 200.00);
select * from rates;
season start_date end_date rate
Low 2007-01-01 2007-04-30 100
High 2007-05-01 2007-08-31 150
Peak 2007-09-01 2007-12-21 200
我将忽略您在问题中给出的日期，并假设客户没有及时倒退.
I'm going to ignore the dates you have given in your question and the assume the client is not travelling backwards in time.
select
date_, rate
from calendar
join rates
on date_ >= start_date and date_ <= end_date
where date_ between '2007-04-29' and '2007-5-01'
;
date_ rate
2007-04-29 100
2007-04-30 100
2007-05-01 150
select
sum(rate)
from calendar
join rates
on date_ >= start_date and date_ <= end_date
where date_ between '2007-04-29' and '2007-5-01'
sum(rate)
350
而且，正如您所见，该 sql 非常简洁易读，无需借助函数或过程.这将能够正确扩展并处理更复杂的问题.此外，由于数据是基于表的，因此可以使用引用检查.
And, as you can see the sql is quite concise and readable without resorting to functions or procedures. This will be able to scale properly and handle more complex questions. Also, it enables referential checking to be used since the data is table based.
这篇关于不同季节的酒店房价的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！