查询中来自多个表的累计和

Question

我有这个查询，以列出与特定优惠券订购的woocommerce订单，我想添加列与累计金额。我试过SUM(_order_total) OVER(ORDER BY po.ID)，但它不起作用。

SELECT DATE_FORMAT(po.post_date, '%Y-%m-%d ') as Order_date,
       po.ID AS Order_Number,
       MAX(CASE WHEN pmo.meta_key = '_order_total' AND po.ID = pmo.post_id THEN pmo.meta_value END) AS Total
FROM `wpl9_posts` AS pc
INNER JOIN `wpl9_postmeta` AS pmc ON  pc.`ID` = pmc.`post_id`
INNER JOIN `wpl9_woocommerce_order_items` AS woi ON pc.post_title = woi.order_item_name
    AND woi.order_item_type = 'coupon'
INNER JOIN `wpl9_posts` AS po ON woi.order_id = po.ID
    AND po.post_type = 'shop_order'
    AND po.post_status IN ('wc-completed', 'wc-processing', 'wc-refunded') -- Added needed order status over here.
INNER JOIN `wpl9_postmeta` AS pmo ON po.ID = pmo.post_id
WHERE pc.post_type = 'shop_coupon' AND
      pc.post_title = 'partner001' AND
GROUP BY po.ID
ORDER BY pc.ID DESC

Answer 1

您可以使用变量：

select q.*,
       (@sum := @sum + total) as running_sum
from (<your query here>) q cross join
     (select @sum := 0) params;