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

该段代码的运行结果如上：目的是先查询出订单相应的取消和退货数据，后面考虑再对时间进行处理。

另外一点疑惑：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_joined_return.r_r_amt) as rr_r_amt, 
	sum(od_joined_return.r_r_qty) as rr_r_qty 
from 
	(select 
		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.创单时间, 
 			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.创单时间) 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.创单时间) as od_joined_return 
left join 
	(select 
		order_line.订单号, 
		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.订单号, order_line.创单时间) as od_grouped_cancel 
on od_joined_return.订单号 = od_grouped_cancel.订单号 
group by od_joined_return.创单时间 
;

qq_34644971

怎样在ubuntu18.04中安装java1.4

2条回答

为你推荐