又一个联合查询

select a.id,a.sale_orderid,a.orderid,
to_char(a.lrrq,'yyyy-mm-dd') as lrrq,a.lrr,
xmltype.extract(a.xml_content,'/prodOrder/@clientComId').getstringval() clientComId,
xmltype.extract(a.xml_content,'/prodOrder/@clientComName').getstringval() clientComName,
substr(xmltype.extract(a.xml_content,'/prodOrder/@orderDate').getstringval(),1,10) orderDate,
xmltype.extract(a.xml_content,'/prodOrder/@proCompanyCode').getstringval() proCompanyCode,
xmltype.extract(a.xml_content,'/prodOrder/@proCompanyName').getstringval() proCompanyName,
xmltype.extract(a.xml_content,'/prodOrder/@flag').getstringval() flag,
xmltype.extract(value(b),'/orderDetail/@id').getstringval() did,
xmltype.extract(value(b),'/orderDetail/@produceId').getstringval() produceId,
xmltype.extract(value(b),'/orderDetail/@produceName').getstringval() produceName,
xmltype.extract(value(b),'/orderDetail/@cere__mode').getstringval() cere_mode,
xmltype.extract(value(b),'/orderDetail/@unit').getstringval() unit,
xmltype.extract(value(b),'/orderDetail/@number').getstringval() num,
xmltype.extract(value(b),'/orderDetail/@price').getstringval() price,
substr(xmltype.extract(value(b),'/orderDetail/@endDate').getstringval(),1,10) endDate,
xmltype.extract(value(b),'/orderDetail/@storeCode').getstringval() storeCode,
xmltype.extract(value(b),'/orderDetail/@storeContent').getstringval() storeContent,
xmltype.extract(value(b),'/orderDetail/@typeContent').getstringval() typeContent,
xmltype.extract(value(b),'/orderDetail/@packaged').getstringval() packaged,
xmltype.extract(value(b),'/orderDetail/@percent').getstringval() percent,
xmltype.extract(value(b),'/orderDetail/@processPrice').getstringval() processPrice,
xmltype.extract(value(b),'/orderDetail/@valiPrice').getstringval() valiPrice,
xmltype.extract(value(b),'/orderDetail/@outJoinPrice').getstringval() outJoinPrice,
XMLTYPE.EXTRACT(VALUE(b),'/orderDetail/@outJoinSum').getstringval() outJoinSum,
nvl(sum_bcp,0) sum_bcp,
nvl(sum_cplp,0) sum_cplp,
nvl(sum_cpblp,0) sum_cpblp,
(nvl(sum_bcp,0)+nvl(sum_cplp,0)+nvl(sum_cpblp,0)) sum_all
from produce_order a,
table(xmlsequence(xmltype.extract(a.xml_content,'/prodOrder/list[@id="detail"]/orderDetail'))) b, 
  (
    SELECT 
    sum(XMLTYPE.EXTRACT(VALUE(b),'/detail/@amount').getstringval()) sum_cpblp, 
    XMLTYPE.EXTRACT(A.XML_CONTENT,'/order/@produceOrderid').getstringval() bad_orderid
    FROM badgoods_instore_order A,TABLE(xmlsequence(XMLTYPE.EXTRACT(A.xml_content,'/order/list[@id="detail"]/detail'))) b 
    where check_state='2'
    GROUP BY XMLTYPE.EXTRACT(A.XML_CONTENT,'/order/@produceOrderid').getstringval()
  ) bad,
  (
    SELECT 
    sum(XMLTYPE.EXTRACT(VALUE(b),'/halfgoodsDetail/@sum').getstringval()) sum_bcp,
    produce_orderid half_orderid
		FROM prod_halfgoods_instore_order A,TABLE(xmlsequence(XMLTYPE.EXTRACT(A.xml_content,'/halfgoodsOrder/list[@id="detail"]/halfgoodsDetail'))) b 
    where  XMLTYPE.EXTRACT(A.XML_CONTENT,'/halfgoodsOrder/@checkstate').getstringval()='2'
    GROUP BY produce_orderid
  ) half,
  (
    SELECT sum(XMLTYPE.EXTRACT(VALUE(b),'/produceGoodsInstoreOrderDetail/totalNum/text()').getstringval()) sum_cplp,
    produce_orderid good_orderid
		from produce_goods_instore_order a,
		TABLE(xmlsequence(XMLTYPE.EXTRACT(A.xml_content,'/produceGoodsInstoreOrder/list[@id="produceGoodsInstoreOrderDetail"]/produceGoodsInstoreOrderDetail'))) b
     WHERE XMLTYPE.EXTRACT(A.XML_CONTENT,'/produceGoodsInstoreOrder/@check__state').getstringval()='2'
     GROUP BY produce_orderid 
  ) good
WHERE 
A.orderid=bad.bad_orderid(+) and
a.orderid=half.half_orderid(+) and
a.orderid=good.good_orderid(+)