题解 | 电商平台需要对商家的销售业绩、退款情况和客户满意度进行综合评估

SELECT 
    m.merchant_id,
    m.merchant_name,  
    total_sales_amount,
    total_refund_amount,
    average_satisfaction_score
FROM merchants_underline m
LEFT JOIN (
    SELECT merchant_id, SUM(sale_amount) total_sales_amount
    FROM sales_underline
    GROUP BY merchant_id
) s ON s.merchant_id = m.merchant_id
LEFT JOIN (
    SELECT merchant_id, SUM(refund_amount) total_refund_amount
    FROM refunds_underline
    GROUP BY merchant_id
) r ON r.merchant_id = m.merchant_id
LEFT JOIN (
    SELECT merchant_id, ROUND(AVG(satisfaction_score), 2) average_satisfaction_score
    FROM satisfaction_underline sa
    GROUP BY merchant_id
) sa ON sa.merchant_id = m.merchant_id
order by m.merchant_id

主查询中关联的子查询已经按 merchant_id 完成了聚合，左关联后主表 merchants_underline 的每一行只会匹配到各子查询的 0 行或 1 行数据，主查询无需再分组。