MySQL中基于列查询关联行：获取指定日期销售及对应退货记录

首先纠正你现有查询的一个明显错误：原查询中order.id=order_item.id是错误的，应该关联order.id=order_item.order_id，因为order_item的order_id才是关联订单的外键。

方法一：左连接关联退货订单项（基础版） #

不需要用内联子查询，直接通过左连接将销售订单项与对应的退货订单项关联，这样能保留所有符合日期范围的销售记录，即使没有退货也会显示NULL：

SELECT
    -- 销售订单及订单项信息
    sale_order.id AS sale_order_id,
    sale_item.id AS sale_item_id,
    sale_order.created_date AS sale_created_date,
    sale_item.subtotal AS sale_subtotal,
    -- 退货订单项信息（如果有）
    return_item.id AS return_item_id,
    return_order.created_date AS return_created_date,
    return_item.subtotal AS return_subtotal
FROM
    `order` AS sale_order
JOIN
    order_item AS sale_item ON sale_order.id = sale_item.order_id
LEFT JOIN
    order_item AS return_item 
    ON sale_order.id = return_item.original_order_id 
    AND sale_item.id = return_item.original_order_item_id
    AND return_item.type = 'RETURN'
LEFT JOIN
    `order` AS return_order ON return_item.order_id = return_order.id
WHERE
    sale_order.created_date BETWEEN '2020-01-01 00:00:00' AND '2021-01-01 00:00:00'
    AND sale_order.type = 'SALE'
    AND sale_item.type = 'SALE'

方法二：聚合退货金额（统计版） #

如果需要统计每个销售记录对应的总退货金额、退货次数，可以用GROUP BY结合聚合函数：

SELECT
    sale_order.id AS sale_order_id,
    sale_item.id AS sale_item_id,
    sale_order.created_date AS sale_created_date,
    sale_item.subtotal AS sale_subtotal,
    SUM(IFNULL(return_item.subtotal, 0)) AS total_return_subtotal,
    COUNT(return_item.id) AS return_count
FROM
    `order` AS sale_order
JOIN
    order_item AS sale_item ON sale_order.id = sale_item.order_id
LEFT JOIN
    order_item AS return_item 
    ON sale_order.id = return_item.original_order_id 
    AND sale_item.id = return_item.original_order_item_id
    AND return_item.type = 'RETURN'
WHERE
    sale_order.created_date BETWEEN '2020-01-01 00:00:00' AND '2021-01-01 00:00:00'
    AND sale_order.type = 'SALE'
    AND sale_item.type = 'SALE'
GROUP BY
    sale_order.id, sale_item.id, sale_order.created_date, sale_item.subtotal

性能优化建议 #

为了让查询更高效，建议创建以下索引：

• order(type, created_date, id)：加速筛选销售订单和日期范围
• order_item(order_id, type, id)：加速关联销售订单项
• order_item(original_order_id, original_order_item_id, type)：快速定位对应销售的退货记录

关于内联子查询的疑问 #

不需要用内联子查询，直接用左连接的方式比子查询更直观，且在大多数数据库中性能更优——数据库优化器通常能更好地优化连接查询的执行计划。

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