根据需求要把包裹明细表(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
------解决方案--------------------
关注
------解决方案--------------------
满屏都是蚂蚁
------解决方案--------------------