谢谢您的回答,马丁给您提供了2行,但是没有几天…埃米利奥(Emilio)的回答让我开始思考如何设置费率。我将费率表更改为日期格式,而不是日期时间,并使rate_end_date与下一个rate_start_date在同一天。
0 66 2011-01-01 2011-04-15
1 70 2011-04-15 2011-06-01
2 80 2011-06-01 2011-07-01
3 100 2011-07-01 2011-09-01
4 80 2011-09-01 2011-10-01
5 70 2011-10-01 2011-11-01
6 45 2011-11-01 2012-01-01
然后放下+1并
SELECT rates.rate_id, rate_start_date, rate_end_date, rate_price,
(DATEDIFF( IF (rate_end_date > '2011-04-16' , '2011-04-16', rate_end_date),
IF ( rate_start_date < '2011-04-14' , '2011-04-14' , rate_start_date )) )
AS days FROM rates WHERE rate_start_date <= '2011-04-16'
AND rate_end_date > '2011-04-14'
ORDER BY rate_price ASC
生产的
rate_id rate_start_date rate_end_date rate days
0 2011-01-01 2011-04-15 66 1
1 2011-04-15 2011-06-01 70 1
以及从4月1日到8日没有重叠率的查询:
SELECT rates.rate_id, rate_start_date, rate_end_date, rate_price,
(DATEDIFF( IF (rate_end_date > '2011-04-08' , '2011-04-08', rate_end_date),
IF ( rate_start_date < '2011-04-01' , '2011-04-01' , rate_start_date )) )
AS days FROM rates WHERE rate_start_date <= '2011-04-08'
AND rate_end_date > '2011-04-01'
ORDER BY rate_price ASC
产生:
rate_id rate_start_date rate_end_date rate days
0 2011-01-01 2011-04-15 66 7
感谢agiain的帮助!