客户存欠统计的sql语句

实现客户存欠统计

关联两张表，订单表和客户来款表。如下图：

 

 

orderid字段为订单号，格式：前缀+时间+流水号

Incoming字段为每次来款金额

amount字段为一种商品的总价格

 

一张订单可以订购几种商品，同时可以分批来款

sql如下：

select orderid,sum(total)as total from
((select orderid,sum(0-amount)as total from tb_order group by orderid,buyid)
union all (select orderid,sum(incoming) as total from tb_incoming_balance group by orderid))as temp
group by orderid