如何在LEFT JOIN中仅计算唯一行并正确求和seller_payout？

原查询中seller_payout返回错误值，原因是多表关联导致orders行被重复统计：

SELECT buyer_charges_histories.*,
       count(DISTINCT auction_bids.id) AS opportunities_count,
       count(DISTINCT offers.id) AS offers_count,
       count(DISTINCT orders.id) AS orders_count,
       count(DISTINCT CASE WHEN orders.status_id = 1 THEN orders.id ELSE NULL END) AS pending_orders_count,
       count(DISTINCT CASE WHEN orders.status_id = 3 THEN orders.id ELSE NULL END) AS completed_orders_count,
       count(DISTINCT CASE WHEN orders.status_id = 5 THEN orders.id ELSE NULL END) AS canceled_orders_count,
       sum(CASE WHEN orders.status_id = 3 THEN orders.seller_payout ELSE NULL END) as seller_payout
FROM `buyer_charges_histories`
LEFT JOIN `quotes` ON `quotes`.`buyer_charges_history_id` = `buyer_charges_histories`.`id`
LEFT JOIN `offers` ON `quotes`.`id` = `offers`.`quote_id`
AND `offers`.`draft` = 0
AND (`offers`.`enterprise` = 0
     OR `offers`.`enterprise` IS NULL)
LEFT JOIN `auction_bids` ON `offers`.`auction_id` = `auction_bids`.`auction_id`
LEFT JOIN `orders` ON `offers`.`order_id` = `orders.id`
AND `orders`.`test` IS NULL
WHERE `buyer_charges_histories`.`buyer_id` = 1
  AND `buyer_charges_histories`.`buyer_id` IS NOT NULL
GROUP BY `buyer_charges_histories`.`id`
ORDER BY `buyer_charges_histories`.`id` DESC

通过以下查询已确认正确的seller_payout结果：

select sum(seller_payout) from orders where exists (
    select * from offers where offers.order_id = orders.id and offers.draft = 0 and (offers.enterprise = 0 or offers.enterprise is null) and exists (
        select * from quotes where quotes.id = offers.quote_id and quotes.buyer_charges_history_id = 136
    )
) and test is null and status_id = 3

解决方法 #

要确保仅对唯一的orders行求和，有两种常用方案：

方案1：先对orders去重再关联

将orders表的关联改为子查询，先筛选出唯一的有效订单记录，避免重复行被带入主查询：

SELECT buyer_charges_histories.*,
       count(DISTINCT auction_bids.id) AS opportunities_count,
       count(DISTINCT offers.id) AS offers_count,
       count(DISTINCT orders.id) AS orders_count,
       count(DISTINCT CASE WHEN orders.status_id = 1 THEN orders.id ELSE NULL END) AS pending_orders_count,
       count(DISTINCT CASE WHEN orders.status_id = 3 THEN orders.id ELSE NULL END) AS completed_orders_count,
       count(DISTINCT CASE WHEN orders.status_id = 5 THEN orders.id ELSE NULL END) AS canceled_orders_count,
       sum(CASE WHEN orders.status_id = 3 THEN orders.seller_payout ELSE NULL END) as seller_payout
FROM `buyer_charges_histories`
LEFT JOIN `quotes` ON `quotes`.`buyer_charges_history_id` = `buyer_charges_histories`.`id`
LEFT JOIN `offers` ON `quotes`.`id` = `offers`.`quote_id`
AND `offers`.`draft` = 0
AND (`offers`.`enterprise` = 0
     OR `offers`.`enterprise` IS NULL)
LEFT JOIN `auction_bids` ON `offers`.`auction_id` = `auction_bids`.`auction_id`
-- 改为关联去重后的orders子查询
LEFT JOIN (
    SELECT id, status_id, seller_payout 
    FROM orders 
    WHERE test IS NULL 
    GROUP BY id, status_id, seller_payout -- 确保每个order只保留一行
) AS orders ON `offers`.`order_id` = `orders`.`id`
WHERE `buyer_charges_histories`.`buyer_id` = 1
  AND `buyer_charges_histories`.`buyer_id` IS NOT NULL
GROUP BY `buyer_charges_histories`.`id`
ORDER BY `buyer_charges_histories`.`id` DESC

方案2：使用SUM结合DISTINCT与唯一标识拼接

如果无法提前去重，可以通过拼接order.id和seller_payout确保每个订单的 payout 只被计算一次，再拆分求和：

SELECT buyer_charges_histories.*,
       count(DISTINCT auction_bids.id) AS opportunities_count,
       count(DISTINCT offers.id) AS offers_count,
       count(DISTINCT orders.id) AS orders_count,
       count(DISTINCT CASE WHEN orders.status_id = 1 THEN orders.id ELSE NULL END) AS pending_orders_count,
       count(DISTINCT CASE WHEN orders.status_id = 3 THEN orders.id ELSE NULL END) AS completed_orders_count,
       count(DISTINCT CASE WHEN orders.status_id = 5 THEN orders.id ELSE NULL END) AS canceled_orders_count,
       -- 拼接order.id和payout，去重后提取数值求和
       sum(DISTINCT CASE WHEN orders.status_id = 3 THEN CAST(SUBSTRING_INDEX(CONCAT(orders.id, '|', orders.seller_payout), '|', -1) AS DECIMAL) ELSE NULL END) as seller_payout
FROM `buyer_charges_histories`
LEFT JOIN `quotes` ON `quotes`.`buyer_charges_history_id` = `buyer_charges_histories`.`id`
LEFT JOIN `offers` ON `quotes`.`id` = `offers`.`quote_id`
AND `offers`.`draft` = 0
AND (`offers`.`enterprise` = 0
     OR `offers`.`enterprise` IS NULL)
LEFT JOIN `auction_bids` ON `offers`.`auction_id` = `auction_bids`.`auction_id`
LEFT JOIN `orders` ON `offers`.`order_id` = `orders.id`
AND `orders`.`test` IS NULL
WHERE `buyer_charges_histories`.`buyer_id` = 1
  AND `buyer_charges_histories`.`buyer_id` IS NOT NULL
GROUP BY `buyer_charges_histories`.`id`
ORDER BY `buyer_charges_histories`.`id` DESC

推荐方案1，逻辑更清晰且性能更优，避免了字符串拼接和类型转换的额外开销。

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