从MySQL中获取指定日期区间内价格总和的最优方案

嘿，这个场景在按时间段计费的业务里太常见了！我来给你梳理下最靠谱的解决思路，保证高效又准确：

核心逻辑拆解 #

你的价格是按固定时间段设置的，而查询的区间大概率会和这些时间段部分重叠，所以关键就是：

1. 找出每个价格时间段和查询区间的重叠部分
2. 计算重叠天数 × 对应价格
3. 把所有结果加起来就是总价格

具体实现步骤 #

1. 先处理日期格式（重要！）

看你的数据里日期是dd.mm.yyyy的字符串格式，MySQL直接用会出问题，所以第一步要把字符串转成DATE类型，用STR_TO_DATE(col, '%d.%m.%Y')函数。如果能把表的字段类型改成DATE就更好了，省得每次查询都转换，还能加索引提速。

2. 编写查询SQL

以你要查的25.05.2017到05.06.2017为例，SQL代码如下：

SELECT 
    SUM(
        -- 计算重叠天数：结束减起始加1（包含首尾两天）
        (DATEDIFF(
            LEASTR(STR_TO_DATE(EndDate, '%d.%m.%Y'), '2017-06-05'),
            GREATEST(STR_TO_DATE(StartDate, '%d.%m.%Y'), '2017-05-25')
        ) + 1) * Price
    ) AS TotalPrice
FROM 
    your_table -- 替换成你的表名
WHERE 
    -- 过滤完全不重叠的时间段，减少计算量
    STR_TO_DATE(StartDate, '%d.%m.%Y') <= '2017-06-05'
    AND STR_TO_DATE(EndDate, '%d.%m.%Y') >= '2017-05-25';

3. 关键函数说明

• GREATEST(a,b)：取两个日期里较晚的那个，也就是重叠区间的起始
• LEAST(a,b)：取两个日期里较早的那个，也就是重叠区间的结束
• DATEDIFF(end, start)：计算两个日期之间的天数差，加1是因为要包含起始当天

优化建议 #

• 改字段类型：把StartDate和EndDate改成DATE类型，避免每次查询都做字符串转换
• 加索引：给这两个字段加联合索引INDEX idx_date_range (StartDate, EndDate)，这样WHERE条件能快速过滤掉无关的行，数据量大的时候效率提升明显

验证你的示例数据 #

用你给的两行数据测试：

• 第一行（100元）：重叠区间是25.05.2017到01.06.2017，共8天，贡献8×100=800
• 第二行（150元）：重叠区间是02.06.2017到05.06.2017，共4天，贡献4×150=600
• 总和就是800+600=1400，完全符合预期

内容的提问来源于stack exchange，提问作者Hajrudin