如何找出某个PO净接收的事务

现有如下事务明细PO#1004811

 

 

要求:现用户要求是查询净接收数量,按上图,净接收数量应该是450pcs

 

 

措施:

select rt.transaction_id,
       ph.segment1,
       rt.transaction_type,
       rt.quantity,
       rt.destination_type_code,
       rt.primary_quantity
  from po.rcv_transactions rt,
       po.po_headers_all ph
 where ph.segment1='1004811'
   and rt.destination_type_code = 'RECEIVING'
   and rt.po_header_id=ph.po_header_id
   and rt.parent_transaction_id=-1
   and not exists
 (select 'T'
          from po.rcv_transactions rt1       
         where rt.transaction_id = rt1.parent_transaction_id
           and rt1.po_header_id = rt.po_header_id
           and rt1.destination_type_code = rt.destination_type_code)

 

但这样的查询效率超慢(原因是rt.parent_transaction_id=-1)

 

改进后的SQL如下

 

select rt.transaction_id,
       ph.segment1,
       rt.transaction_type,
       rt.quantity,
       rt.destination_type_code,
       rt.primary_quantity
  from po.rcv_transactions rt,
       po.po_headers_all ph
 where ph.segment1='1004811'
   and rt.destination_type_code = 'RECEIVING'
   and rt.po_header_id = ph.po_header_id
   and not exists
 (select 'T'
          from po.rcv_transactions rt1
         where rt.transaction_id = rt1.parent_transaction_id
           and rt1.po_header_id = rt.po_header_id
           and rt1.destination_type_code = rt.destination_type_code)
     
   and not exists
 (select 'T'
          from po.rcv_transactions rt1
         where rt1.transaction_id = rt.parent_transaction_id
           and rt1.po_header_id = rt.po_header_id
           and rt1.destination_type_code = rt.destination_type_code)

 

 

 

注:在使用Not exists对同一张tables做判别时,里外的查询条件要保持一致.