当前位置: 代码迷 >> Sql Server >> 帮忙优化如上sql多谢
  详细解决方案

帮忙优化如上sql多谢

热度:158   发布时间:2016-04-25 01:17:26.0
帮忙优化如下sql谢谢!
根据需求要把包裹明细表(x_tm_package_detail)中的每个包裹状态(换货,拒收,退货,正常)按不同的时间分组显示。每个单所在的包裹操作记录(换货,拒收,退货字段为OPERATE_TYPE)状态的数据都在记录表里面(x_tm_refund_item),我做如下sql的思路是查记录表中所有的数据并连查询包裹时细表中正常包裹的数据,正常状态的判断在如下sql中红色部分,
运行如下sql如果有几百万会很慢,请sql高手优化一下,急~~~~~~~~~~~~
想清楚表结构的可以看以前发贴:
http://topic.csdn.net/u/20120110/15/efff51b6-8f57-46b0-8b04-f9045aded6f8.html
http://topic.csdn.net/u/20120110/15/efff51b6-8f57-46b0-8b04-f9045aded6f8.html

select * from (
  select package_id,OPERATE_TYPE,OPERATE_TIME,ITEM_QUANTITY,PACKAGE_NUMBER,order_id,deliver_name,
  deliver_no,order_code,pay_type,buyer_nick,full_name,phone,mobile,item_codes,item_name,sku_code,deliver_address from
  (
  select pack.package_id,item.OPERATE_TYPE,item.OPERATE_TIME,item.order_detail_id,sum(nvl(item.ITEM_QUANTITY,0)) as ITEM_QUANTITY,
  pack.PACKAGE_NUMBER,pack.order_id,nvl(pack.deliver_name,'') as deliver_name,pack.deliver_no,d.order_code,d.pay_type,d.buyer_nick,
  addr.full_name,addr.phone,addr.mobile,de.item_codes,packde.item_name,packde.sku_code,pack.deliver_address 
  from x_tm_refund_item item
  inner join x_tm_package pack on pack.package_id=item.package_id
  inner join x_tm_order d on d.order_id=item.order_id
  inner join x_tm_deliver_addr addr on addr.id =d.order_deliver_addr_id 
  inner join x_tm_order_detail de on de.order_detail_id=item.order_detail_id
  left join x_tm_package_detail packde on packde.order_detail_id=item.order_detail_id and packde.package_id=item.package_id and packde.sku_code=item.sku_code
   
  where pack.status=0 and pack.logistics_status=2 and nvl(packde.order_detail_id,0)>0
   
  group by pack.package_id,packde.sku_code,pack.order_id,item.order_detail_id,item.OPERATE_TYPE,item.OPERATE_TIME,pack.PACKAGE_NUMBER,
  deliver_name,pack.deliver_no,d.pay_type,d.buyer_nick,addr.full_name,addr.phone,de.item_codes,packde.item_name,d.order_code,addr.mobile,pack.deliver_address 
  )AA
  union all
  select package_id,OPERATE_TYPE,OPERATE_TIME,ITEM_QUANTITY,PACKAGE_NUMBER,order_id,deliver_name,
  deliver_no,order_code,pay_type,buyer_nick,full_name,phone,mobile,item_codes,item_name,sku_code,deliver_address from
  (
  select BB.package_id,3 as OPERATE_TYPE,BB.GMT_MODIFIED as OPERATE_TIME,packde.order_detail_id,
  (packde.QUANTITY-nvl(packde.RETURN_QUANTITY,0)-nvl(packde.EXCHANGE_QUANTITY,0)-nvl(packde.REJECTION_QUANTITY,0)) as ITEM_QUANTITY,
  BB.PACKAGE_NUMBER,BB.order_id,BB.deliver_name,BB.deliver_no,d.order_code,d.pay_type,d.buyer_nick,addr.full_name,addr.phone,
  addr.mobile,de.item_codes,packde.item_name,packde.sku_code,BB.deliver_address
  from (select pack.package_id,pack.PACKAGE_NUMBER,pack.order_id,nvl(pack.deliver_name,'') as deliver_name,pack.deliver_address,pack.deliver_no,GMT_MODIFIED
  from x_tm_package pack where pack.logistics_status = 2 and pack.status = 0) BB
  left join x_tm_package_detail packde on BB.package_id=packde.package_id
  inner join X_TM_ORDER d on BB.order_id=d.order_id
  inner join x_tm_deliver_addr addr on addr.id=d.order_deliver_addr_id
  inner join x_tm_order_detail de on de.order_detail_id=packde.order_detail_id
  where nvl(packde.order_detail_id,0)>0 and (packde.quantity-nvl(packde.return_quantity,0)-nvl(packde.rejection_quantity,0)-nvl(packde.exchange_quantity,0)>0)
  )CC
  )BTA where 1=1

------解决方案--------------------
关注
------解决方案--------------------
满屏都是蚂蚁
------解决方案--------------------
  相关解决方案