mysql group by 和 sum 结果来自两个表的2个字段

Question

我有 bills、bill_details、customers 和 suppliers 表我需要进行查询以返回 customers 和 suppliers 表

中提交的 area 的总销售额和总返回

我需要所有客户和供应商的相同面积的总和

select c.area,s.area,
        sum( IF(b.bill_type = 'sales', bd.quantity*(bd.price-bd.discount), 0) ) as totalSales,
        sum( IF(b.bill_type = 'salesReturns', bd.quantity*(bd.price-bd.discount), 0) ) as totalReturns,
        sum( IF(b.bill_type = 'sales', bd.quantity, 0) ) as sales,
        sum( IF(b.bill_type = 'salesReturns', bd.quantity, 0) ) as returns
    from adsl_bill b
    left join bill_details bd on bd.bill_num = b.bill_num && bd.bill_type = b.bill_type
    left join customers c on b.customer  = c.id && b.customer_type = 1
    left join suppliers s on b.customer  = s.id && b.customer_type = 2

group by area

我知道按区域分组是错误的陈述，但我只需要对 s.area 和 c.area 进行分组和求和

Answer 1

使用

select ifnull(c.area, s.area) as area,
        sum( IF(b.bill_type = 'sales', bd.quantity*(bd.price-bd.discount), 0) ) as totalSales,
        sum( IF(b.bill_type = 'salesReturns', bd.quantity*(bd.price-bd.discount), 0) ) as totalReturns,
        sum( IF(b.bill_type = 'sales', bd.quantity, 0) ) as sales,
        sum( IF(b.bill_type = 'salesReturns', bd.quantity, 0) ) as returns
    from adsl_bill b
    left join bill_details bd on bd.bill_num = b.bill_num && bd.bill_type = b.bill_type
    left join customers c on b.customer  = c.id && b.customer_type = 1
    left join suppliers s on b.customer  = s.id && b.customer_type = 2
group by area