子查询很多，可以怎么优化？

[DDRsun3]

如题，
中间件XML文件的SQL如下：查20行左右的数据要4秒左右，怎么优化一下？谢谢

select * from (
select pt_user_id,
       pt_user_name,
       contract_id,
       min(anr) anr,
       pay_style,
       period_num,
       (select qh_res_id from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1) qh_res_id,
       (select delivery_flag from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1) cer_self_take,
       b_typename,
       total_qty,
       make_date,
       valid_date,
       (select contract_intent_status from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1) ht_status,
       (select delivery_e_name from pnf_contract_delivery c where c.type_id = '1' and c.contract_id is not null and c.customer_id=a.customer_id and c.contract_id = a.contract_id and rownum<=1) qr_man,
       a.contract_status co_status,
       customer_id,
       cust_name,
       b_typeid,
       (select sales_contract_id from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1)  sales_contract_id,
       (select sh_name from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1)  sh_name,
       (select end_trans_dw from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1)  end_trans_dw,
      (select end_trans_dw_name from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1)  end_trans_dw_name
     (select end_username from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1)  end_username
 from
     (select distinct * from v_t_s_contract_d where contract_type = '10' and (pay_style = '10' or pay_style = '20') order by make_date desc) a 
group by pt_user_id,pt_user_name,contract_id,pay_style,period_num,b_typename,total_qty,make_date,valid_date,contract_status,customer_id,cust_name,b_typeid)
where 1=1;

[居然有人说长得比我帅]

 (select qh_res_id from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1) qh_res_id,
       (select delivery_flag from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1) cer_self_take,
       b_typename,
       total_qty,
       make_date,
       valid_date,
       (select contract_intent_status from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1) ht_status,

这一段只是要取一个表的三个字段就做了3次子查询，而且条件都一样得 其实可以把全部表列出，做成select * from a,b,c where a.xx=b.xx and b.xx=c.xx形式啊

[DDRsun3]

引用 1 楼 gioh0022 的回复:

 (select qh_res_id from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1) qh_res_id,
       (select delivery_flag from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1) cer_self_take,
       b_typename,
       total_qty,
       make_date,
       valid_date,
       (select contract_intent_status from bs.etb_v_order_card_term b where b.sale_contract_id = a.contract_id and b.pt_user_id = a.pt_user_id and rownum<=1) ht_status,

这一段只是要取一个表的三个字段就做了3次子查询，而且条件都一样得 其实可以把全部表列出，做成select * from a,b,c where a.xx=b.xx and b.xx=c.xx形式啊

你是说这样：？

select qh_res_id,
        cer_self_take,
        ht_status 
from  bs.etb_v_order_card_term b
where b.sale_contract_id = a.contract_id 
  and b.pt_user_id = a.pt_user_id and rownum<=1

但是我要分三个字段的数据来显示，在一个子查询怎么写？可以帮我做个例不？

[shasha483]

WITH sql1 AS (SELECT QH_RES_ID
                  FROM BS.ETB_V_ORDER_CARD_TERM B
                 WHERE B.SALE_CONTRACT_ID = A.CONTRACT_ID
                   AND B.PT_USER_ID = A.PT_USER_ID
                   AND ROWNUM <= 1)
SELECT *
  FROM (SELECT PT_USER_ID,
               PT_USER_NAME,
               CONTRACT_ID,
               MIN(ANR) ANR,
               PAY_STYLE,
               PERIOD_NUM,
               (SELECT QH_RES_ID FROM sql1) QH_RES_ID,
               (SELECT DELIVERY_FLAG FROM sql1) CER_SELF_TAKE,
               B_TYPENAME,
               TOTAL_QTY,
               MAKE_DATE,
               VALID_DATE,
               (SELECT CONTRACT_INTENT_STATUS  FROM sql1) HT_STATUS,
               ....
后边的自己写吧。。同样条件的sql不在重复查询

[shasha483]

可参考。。 http://blog.csdn.net/a9529lty/article/details/4923957/

[zhuhl2011]

同样的子查询 多次检索的时候 用WITH 方法确实不错 你还是先看看执行计划，然后看下哪里的 cost比较大 一点点去改