当前位置: 代码迷 >> Oracle开发 >> SQL 优化,该怎么解决
  详细解决方案

SQL 优化,该怎么解决

热度:481   发布时间:2016-04-24 07:34:48.0
SQL 优化
SELECT a.opp_province,
  a.area_type,
  a.opp_local,
  a.cust_manager_name,
  (CASE
  WHEN length(a.cust_man_dept) != lengthb(a.cust_man_dept) THEN
  a.cust_man_dept
  ELSE
  (SELECT d.dept_name
  FROM th_int_dept d
  WHERE d.row_id = a.cust_man_dept)
  END) AS cust_man_dept_name,
  a.cust_name,
  a.cust_id,
  a.cust_indu,
  (CASE
  WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
  (SELECT t.contact_name
  FROM th_contact t
  LEFT JOIN th_customer cust ON (cust.row_id = t.work_unit_id)
  LEFT JOIN th_party pt ON (pt.row_id = cust.party_id)
  WHERE pt.party_code = a.cust_id
  AND ROWNUM = 1)
  ELSE
  (SELECT t.cust_contact
  FROM tm_province_cust_temp t
  WHERE t.cust_id = a.cust_id
  AND t.opp_id = a.opp_id)
  END) AS cust_link_man,
  (CASE
  WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
  (SELECT t.office_phone
  FROM th_contact t
  LEFT JOIN th_customer cust ON (cust.row_id = t.work_unit_id)
  LEFT JOIN th_party pt ON (pt.row_id = cust.party_id)
  WHERE pt.party_code = a.cust_id
  AND ROWNUM = 1)
  ELSE
  (SELECT t.cust_tel
  FROM tm_province_cust_temp t
  WHERE t.cust_id = a.cust_id
  AND t.opp_id = a.opp_id)
  END) AS cust_link_phone,
  (CASE
  WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
  (SELECT t.email_addr
  FROM th_contact t
  LEFT JOIN th_customer cust ON (cust.row_id = t.work_unit_id)
  LEFT JOIN th_party pt ON (pt.row_id = cust.party_id)
  WHERE pt.party_code = a.cust_id
  AND ROWNUM = 1)
  ELSE
  (SELECT t.cust_email
  FROM tm_province_cust_temp t
  WHERE t.cust_id = a.cust_id
  AND t.opp_id = a.opp_id)
  END) AS cust_link_email,
  (CASE
  WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
  ''
  ELSE
  (SELECT t.cust_property
  FROM tm_province_cust_temp t
  WHERE t.cust_id = a.cust_id
  AND t.opp_id = a.opp_id)
  END) AS cust_attr,
  a.opp_id,
  a.opp_name,
  (SELECT p.display_value
  FROM pb_list_of_value p
  WHERE p.TYPE = 'OPP_DEGREE'
  AND p.language_flag = 'CHS'
  AND p.stand_code = a.opp_imp) AS opp_imp,
  (SELECT p.display_value
  FROM pb_list_of_value p
  WHERE p.TYPE = 'OPP_PHASE'
  AND p.language_flag = 'CHS'
  AND p.stand_code = a.opp_phase) AS opp_phase,
  a.opp_forsign_time,
  (SELECT p.display_value
  FROM pb_list_of_value p
  WHERE p.TYPE = 'OPP_PROJ_TYPE'
  AND p.language_flag = 'CHS'
  AND p.stand_code = a.project_type) AS project_type,
  (SELECT p.display_value
  相关解决方案