MySQL中如何按日期累加计算客户main_product_order的累计值

解决方案 #

方案1：MySQL 8.0+ 版本（推荐，使用窗口函数） #

直接在原有子查询基础上增加累计求和窗口函数即可，窗口按订单日期升序排序实现逐行累加：

SELECT 
    s.*,
    SUM(s.main_product_order) OVER (ORDER BY s.order_date ASC) AS cumulative_main_product
FROM (
    SELECT 
        od.id_order_detail,
        od.id_order,
        COUNT(od.id_order) AS main_product_order,
        od.order_date,
        od.id_customer
    FROM ps_order_detail_with_lens_attribute od
    WHERE od.linkedto = 0
      AND od.id_customer = '52165'
    GROUP BY od.id_order
) s
ORDER BY s.order_date ASC;

方案2：MySQL 5.x 版本（无窗口函数支持场景） #

可以用自关联的方式实现累加计算：

SELECT 
    s1.*,
    SUM(s2.main_product_order) AS cumulative_main_product
FROM (
    SELECT 
        od.id_order_detail,
        od.id_order,
        COUNT(od.id_order) AS main_product_order,
        od.order_date,
        od.id_customer
    FROM ps_order_detail_with_lens_attribute od
    WHERE od.linkedto = 0
      AND od.id_customer = '52165'
    GROUP BY od.id_order
) s1
JOIN (
    SELECT 
        od.id_order_detail,
        od.id_order,
        COUNT(od.id_order) AS main_product_order,
        od.order_date,
        od.id_customer
    FROM ps_order_detail_with_lens_attribute od
    WHERE od.linkedto = 0
      AND od.id_customer = '52165'
    GROUP BY od.id_order
) s2 ON s2.order_date <= s1.order_date
GROUP BY s1.id_order, s1.order_date
ORDER BY s1.order_date ASC;

预期输出结果 #

结合你提供的样本数据，最终输出效果如下：

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