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

select
    m.merchant_id,
    m.merchant_name,
    coalesce(sales.total_sales_amount,0) as total_sales_amount,
    coalesce(r.total_refund_amount,0) as total_refund_amount,
    coalesce(sati.average_satisfaction_score,0) as average_satisfaction_score
from
    merchants_underline m 
    left join (
        select 
            merchant_id,
            round(sum(sale_amount),2) as total_sales_amount 
        from sales_underline 
        group by merchant_id) sales on m.merchant_id=sales.merchant_id
    left join (
        select 
            merchant_id,
            round(sum(refund_amount),2) as total_refund_amount
        from refunds_underline
        group by merchant_id
    ) r on m.merchant_id=r.merchant_id
    left join (
        select
            merchant_id,
            round(avg(satisfaction_score),2) as average_satisfaction_score
        from satisfaction_underline
        group by merchant_id
    ) sati on m.merchant_id=sati.merchant_id
order by
    merchant_id asc

1. 多张表合并时，如果每个key有多条记录，要注意防止笛卡尔积，先单表聚合再合并
2. 在子查询中，注意单张表可以不用"表名.字段"，可以直接写字段
3. 注意查看select字段名是否真的from引用的表