多表查询SQL

例子：

SELECT tmp2.goods_number,tmp2.goods_name,C.product_name,tmp2.chuku
from 
(
  select goods_number,goods_name,sum(goods_quantity) AS chuku from 
  (
  SELECT  distinct A.order_number,B.goods_name,B.goods_number,B.goods_quantity
  from oms_order AS A, oms_order_goods_item as B
  WHERE A.order_number = B.order_number
  AND A.order_status=6
  AND A.warehouse_out_date BETWEEN "2021-09-07 00:00:00" AND "2021-09-09 00:00:00"
  ) as tmp
  group by goods_number
) as tmp2,oms_goods AS C
WHERE tmp2.goods_number = C.goods_code
and C.deleted_at is NULL