假设发布的代码按预期工作，那么扩展rates以在第一行之前和最后一行之后填充扩展，并在没有rates的地方包含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;

字符串
我使用用户定义的变量，因为我不想在代码中重新输入。你可能想创建一个函数，将这些变量作为参数传递给你的目的。src的值是为了帮助调试。
请看链接的结尾，看看cte中的内容。