提问人：user3270967 提问时间：11/8/2023 更新时间：11/11/2023 访问量：50
MySQL计算多个日期范围之间的价格，增加一次费用
MySQL calculation price between multiple dates ranges add charges once
问：
我有一个包含不同时间段价格的表格和一个包含标准价格的表格：
rates:
user_id | start | end | price | charges
1 | 2023-11-13 | 2023-11-19 | 100 | 25
1 | 2023-11-21 | 2023-11-23 | 120 | 30
rates_standard:
user_id | price | charges
1 | 75 | 20
SELECT
SUM(
DateDiff(
Least(r.end + INTERVAL 1 DAY, '2023-11-24'),
Greatest(r.start, '2023-11-10')
) * r.price
) price
FROM
rates r
WHERE
r.end >= '2023-11-10' AND
r.start < '2023-11-24'
通过此查询，我得到 1060 的总价
7 x 100 +
3 x 120
= 1060
如果查询日期不在季节，则应使用标准价格。 我还想添加一次开始日期的费用。如果开始日期不在季节内，则应使用标准费用。 非常感谢您的帮助！
答：
0赞
P.Salmon
11/11/2023
#1
假设已发布的代码按预期工作，然后扩展速率以在第一行之前和最后一行之后填充扩展，并在没有速率的地方包括rates_basic，这就是我的做法。https://dbfiddle.uk/uCJyLRm-
SET @START = '2023-11-13';
SET @end = '2023-11-13';
set @uid = 1;
with cte as
(
select 0 as src,user_id,'1957-01-01' seasonstart, '1957-01-01' seasonend, price from rates_basic
union
select 10 as src,user_id,cast(@start as date) ss,
date_sub(@minstart, interval 1 day) se,
(select price from rates_basic rb where rb.user_id = r.user_id) price
from rates r
where @start < (select min(seasonstart) from rates where user_id = @uid)
union
select 20 as src,user_id,seasonstart,seasonend, price from rates
union
select 25 as src,user_id,
date_add(seasonend,interval 1 day) seasonstar,
date_sub(lead(seasonstart) over (partition by user_id order by seasonstart),interval 1 day) seasonend,
(select price from rates_basic rb where rb.user_id = r.user_id) price
from rates r
union
select 30 as src,user_id,date_add(@maxend,interval 1 day) seasonstart,
@end seasonend,
(select price from rates_basic rb where rb.user_id = r.user_id) price
from rates r
where @end > (select max(seasonend) from rates where user_id = @uid)
)
#select * from cte where src <> 0 and seasonend is not null
SELECT
SUM(
DateDiff(
Least(r.seasonend + INTERVAL 1 DAY, @end),
Greatest(r.seasonstart, @start)
) * r.price
) price
FROM
cte r
WHERE
r.seasonend >= @start AND
r.seasonstart < @end and
src <> 0 and r.seasonend is not null;
我正在使用用户定义的var1ables，因为我不想在代码中重新键入。您可能希望创建一个函数，将这些作为参数传递以供您使用。src 的值有助于调试。
请参阅链接末尾，了解 cte 中的最终内容。
评论