SQL+select+group by+join

#应收应付减去退货售后（只相信商品数量和单价，根据这些具体自己计算）
SELECT
	a.order_sn,
	SUM( b.discount_amount ) AS sum_discount_amount,
	SUM( b.discount_amount - COALESCE ( tmp1.tui_money, 0 ) ) AS sum_total_receivable,
	SUM(
	ROUND( ( b.price * ( b.g_rate * 0.01 ) ), 2 ) * ( b.num - COALESCE ( tmp1.tui_num_total, 0 ) ) 
	) AS sum_total_payable 
FROM
	order a
	JOIN order_goods b ON a.id = b.order_id
	LEFT JOIN (
SELECT
	order_no,
	sku,
	SUM( price * num ) AS tui_money,
	SUM( CASE WHEN a.STATUS IN ( 2, 3, 4 ) THEN b.num ELSE 0 END ) AS tui_num_total 
FROM
	order_after_sales a
	JOIN order_after_sales_detail b ON a.id = b.after_id 
WHERE
	a.type = 1 
GROUP BY
	order_no,
	sku 
	) tmp1 ON b.product_code = tmp1.sku 
	AND a.order_sn = tmp1.order_no 
WHERE
	a.order_sn = 'RT20240105171249431098' 
GROUP BY
	a.order_sn;