weixin_55848786
溯源侵蚀
采纳率100%
2021-03-07 17:19

谢谢!who can tell me, 谁能告诉我这段查询有什么问题?

select sum(order_line.数量) as sq1, sum(grouped_order.sales_qty) as sq2
from order_line, 
(select 订单号, sum(商品总价(元)) as sales_amt, sum(数量) as sales_qty
from order_line
where (财务状态 = '已付款' or 付款方式 = '货到付款')
and 创单时间 between '2021-01-01 00:00:00' and '2021-03-01 00:00:00'
group by 订单号) as grouped_order
where (order_line.财务状态 = '已付款' or order_line.付款方式 = '货到付款')
and order_line.创单时间 between '2021-01-01 00:00:00' and '2021-03-01 00:00:00'
;

因为执行之后不报错,但是用了比较长的时间仍然没有出结果,以上:

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • technologist_03 CSDN技术专家团—feng 1月前

    sql写的有问题,你的order_line、grouped_order表,没有join关联,产生笛卡尔积了,会生成大量数据,肯定很慢

    点赞 1 评论 复制链接分享
  • weixin_55848786 溯源侵蚀 1月前
    select 
    sum(grouped_od.sales_amt), 
    sum(grouped_od.sales_qty), 
    sum(od_grouped_return.return_amt), 
    sum(od_grouped_return.return_qty)
    from
    (select 订单号, sum(商品总价(元)) as sales_amt, sum(数量) as sales_qty
    from order_line
    where (财务状态 = '已付款' or 付款方式 = '货到付款')
    and 创单时间 between '2021-01-01 00:00:00' and '2021-03-01 00:00:00'
    group by 订单号) as grouped_od 
    left join 
    (select 原订单号, sum(应退总金额) as return_amt, sum(退货数量) as return_qty
    from re_refund
    where 创建时间 between '2021-01-01 00:00:00' and '2021-03-01 00:00:00'
    group by 原订单号) as od_grouped_return
    on grouped_od.订单号 = od_grouped_return.原订单号
    ;

    谢谢

    点赞 评论 复制链接分享