如何在一对多表结构下查询指定日期的订单净总价值（不含运费）

问题背景 #

需要统计特定日期/日期范围内，所有订单的总价值（计算逻辑为sub-total减去deduction，对应order_totals表中sub_total与deduction的value之和——因为deduction以负数存储），且排除运费部分。核心难点是sub_total和deduction通过code字段区分，与order表为一对多关联。

现有查询框架：

SELECT 
    // 不确定如何选取sub-total和deductions
FROM `order_total` ot
INNER JOIN `order` o ON ot.order_id = o.id
WHERE DATE(o.date_added) = DATE(NOW());

示例表结构及数据：

order表 #

order_totals表 #

期望查询结果（针对2024-08-29）：

total
1501.7

最优查询方案 #

利用条件聚合直接筛选目标记录并求和，效率最高：

SELECT SUM(ot.value) AS total
FROM `order_total` ot
INNER JOIN `order` o ON ot.order_id = o.id
WHERE DATE(o.date_added) = DATE(NOW())
  AND ot.code IN ('sub_total', 'deduction');

逻辑说明 #

1. 通过ot.code IN ('sub_total', 'deduction')精准过滤出需要计算的两类记录，自动排除运费、total等无关项
2. SUM(ot.value)直接累加符合条件的数值：由于deduction存储为负数（如-35），累加效果等价于sub_total - 抵扣金额，完全匹配需求
3. 若要支持日期范围，只需将WHERE条件修改为o.date_added BETWEEN '2024-08-01' AND '2024-08-31'这类范围判断即可

备选方案（兼容不完整订单） #

如果存在部分订单缺少sub_total或deduction记录的情况，可以先按订单分组计算单订单合计，再汇总：

SELECT SUM(order_total) AS total
FROM (
    SELECT 
        o.id,
        SUM(CASE WHEN ot.code = 'sub_total' THEN ot.value ELSE 0 END) +
        SUM(CASE WHEN ot.code = 'deduction' THEN ot.value ELSE 0 END) AS order_total
    FROM `order` o
    LEFT JOIN `order_total` ot ON o.id = ot.order_id
    WHERE DATE(o.date_added) = DATE(NOW())
    GROUP BY o.id
) AS order_summary;

该方案会为每个订单单独计算合计，即使某订单缺失其中一项记录，对应项会按0计算，保证统计不中断。

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