MySQL查询需求：按订单状态新增已完成/未完成订单合计列

没问题，我帮你调整了原查询，新增了你需要的两个客户订单金额汇总列，同时保留了原有的业务逻辑（筛选未付清订单、按客户分组、按已付金额降序）：

SELECT 
    o.orders_id,
    o.customers_id,
    o.customers_name,
    s.orders_status_name,
    ot.text AS order_total,
    ot.value AS value,
    DATEDIFF(NOW(), payment_data_read_status) AS numdaysleft,
    SUM(IFNULL(op.paid_total, 0)) AS paid_total,
    -- 新增：汇总客户所有已完成订单的总金额
    SUM(CASE WHEN s.orders_status_name = 'Order Complete' THEN ot.value ELSE 0 END) AS completed_orders_sum,
    -- 新增：汇总客户所有非已完成订单的总金额
    SUM(CASE WHEN s.orders_status_name != 'Order Complete' THEN ot.value ELSE 0 END) AS incompleted_orders_sum
FROM orders o
JOIN orders_total ot ON o.orders_id = ot.orders_id
JOIN orders_status s ON o.orders_status = s.orders_status_id
LEFT OUTER JOIN (
    SELECT orders_id, SUM(IFNULL(paid_amount, 0)) AS paid_total 
    FROM orders_payment 
    WHERE confirm_payment = '1' 
    GROUP BY orders_id
) op ON op.orders_id = o.orders_id
WHERE 
    ot.class = 'ot_total' 
    AND s.language_id = '1' 
    AND ROUND(ot.value, 2) != ROUND(IFNULL(op.paid_total, 0), 2)
GROUP BY o.customers_id, o.orders_id, o.customers_name, s.orders_status_name, ot.text, ot.value, payment_data_read_status
ORDER BY paid_total DESC;

关键说明 #

1. completed_orders_sum：通过CASE条件判断，仅当订单状态为Order Complete时，累加该订单的总金额（ot.value），否则加0，最终得到客户所有已完成订单的金额总和。
2. incompleted_orders_sum：逻辑相反，累加所有状态不等于Order Complete的订单金额。你也可以用SUM(ot.value) - completed_orders_sum替代这个CASE语句，计算效率会更高，结果完全一致。
3. 我补充了GROUP BY子句的内容——MySQL 5.7+默认开启ONLY_FULL_GROUP_BY模式，所有非聚合列必须出现在GROUP BY中，否则会触发语法错误，原查询的分组逻辑在严格模式下无法运行，所以这里做了适配调整。

内容的提问来源于stack exchange，提问作者Muhammad Asif Raza