MySQL如何按日期范围结合天数统计订单总价格
按指定日期范围统计公司订单总金额实现方案
核心逻辑
- 首先定义你需要查询的起止日期,记为
@query_start、@query_end - 计算单个订单和查询范围的重叠区间:重叠起始日期取
GREATEST(date_start, @query_start),重叠结束日期取LEAST(date_end, @query_end) - 若重叠起始日期 > 重叠结束日期,说明该订单不在查询范围内,有效天数计为0;否则有效天数为
DATEDIFF(重叠结束日期, 重叠起始日期) + 1(+1是头尾日期都计入,若业务规则到期日不计入可直接去掉+1) - 单订单金额为单日价格乘以有效天数,按公司分组求和即可得到各公司在指定范围内的总金额
实现SQL
-- 定义查询的起止日期,可根据需求修改 SET @query_start = '2021-12-01'; SET @query_end = '2021-12-31'; SELECT company, SUM( price * CASE WHEN LEAST(date_end, @query_end) >= GREATEST(date_start, @query_start) THEN DATEDIFF(LEAST(date_end, @query_end), GREATEST(date_start, @query_start)) + 1 ELSE 0 END ) AS company_total_amount FROM `orders` -- 可选:加过滤条件排除完全不在查询范围内的订单,提升查询效率 -- WHERE date_start <= @query_end AND date_end >= @query_start GROUP BY company;
示例结果验证
针对你给出的示例数据,执行上述SQL后B公司的总金额计算如下:
- item1的重叠区间为2021-12-01至2021-12-10,有效天数10天,金额为
5*10=50 - item2的重叠区间为2021-12-20至2021-12-25,有效天数6天,金额为
10*6=60 - 最终B公司总金额为110
内容的提问来源于stack exchange,提问作者Elexer
相关产品推荐
相关产品推荐

