weixin_55848786
溯源侵蚀
采纳率100%
2021-03-07 18:20

请问:这样的一个查询 对于高效工作是否有实际意义?

如果是有意义的,那是否有改进建议;是否有其他更合适的方法来实现同样或类似的需求/结果?谢谢!

该段代码的运行结果如上:目的是先查询出各来源相应的取消和退货数据。

另外一点疑惑: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.订单来源 
;
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答