如果是有意义的,那是否有改进建议;是否有其他更合适的方法来实现同样或类似的需求/结果?谢谢!
该段代码的运行结果如上:目的是先查询出各来源相应的取消和退货数据。
另外一点疑惑:SQL要实现连续的查询(且互通),只能这样一层一层的嵌套吗?。。。
select
od_joined_return.订单来源,
sum(od_joined_return.r_s_amt) as rr_s_amt,
sum(od_joined_return.r_s_qty) as rr_s_qty,
sum(od_grouped_cancel.cancel_amt) as rr_c_amt,
sum(od_grouped_cancel.cancel_qty) as rr_c_qty,
sum(od_joined_return.r_r_amt) as rr_r_amt,
sum(od_joined_return.r_r_qty) as rr_r_qty,
(sum(coalesce(od_joined_return.r_s_amt, '0')) - sum(coalesce(od_grouped_cancel.cancel_amt, '0')) - sum(coalesce(od_joined_return.r_r_amt, '0'))) as ns_amt,
(sum(coalesce(od_joined_return.r_s_qty, '0')) - sum(coalesce(od_grouped_cancel.cancel_qty, '0')) - sum(coalesce(od_joined_return.r_r_qty, '0'))) as ns_qty
from
(select
grouped_od.订单号,
grouped_od.创单时间,
grouped_od.订单来源,
sum(grouped_od.sales_amt) as r_s_amt,
sum(grouped_od.sales_qty) as r_s_qty,
sum(od_grouped_return.return_amt) as r_r_amt,
sum(od_grouped_return.return_qty) as r_r_qty
from
(select
order_line.订单号,
order_line.创单时间,
order_line.订单来源,
sum(order_line.商品总价(元)) as sales_amt,
sum(order_line.数量) as sales_qty
from order_line
where (order_line.财务状态 = '已付款' or order_line.付款方式 = '货到付款')
and order_line.创单时间 between '2021-01-01 00:00:00' and '2021-03-01 00:00:00'
group by order_line.订单号, order_line.创单时间, order_line.订单来源) as grouped_od
left join
(select
re_refund.原订单号,
re_refund.创建时间,
sum(re_refund.应退总金额) as return_amt,
sum(re_refund.退货数量) as return_qty
from re_refund
where re_refund.创建时间 between '2021-01-01 00:00:00' and '2021-03-01 00:00:00'
group by re_refund.原订单号, re_refund.创建时间) as od_grouped_return
on grouped_od.订单号 = od_grouped_return.原订单号
group by grouped_od.订单号, grouped_od.创单时间, grouped_od.订单来源) as od_joined_return
left join
(select
order_line.订单号,
sum(order_line.商品总价(元)) as cancel_amt,
sum(order_line.数量) as cancel_qty
from order_line
where (order_line.财务状态 = '已付款' or order_line.付款方式 = '货到付款')
and order_line.创单时间 between '2021-01-01 00:00:00' and '2021-03-01 00:00:00'
and order_line.订单行状态 in ('会员取消', '系统取消')
group by order_line.订单号) as od_grouped_cancel
on od_joined_return.订单号 = od_grouped_cancel.订单号
group by od_joined_return.订单来源
;