这语句如何优化
select r2.area_id, r2.org_name,r1.org_id, sum( r1.高血压) 高血压,sum( r1.恶性肿瘤) 恶性肿瘤, sum(r1.糖尿病) 糖尿病,sum( r1.重性精神病) 重性精神病
  from (select case
   
  when s1.org_id is null then
  (case
  when s2.org_id is null then
  (case
  when s3.org_id is null then
  s4.org_id
  else
  s3.org_id
  end) else s2.org_id end) else s1.org_id
   
  end org_id,
  case
  when 高血压 is null then
  0
  else
  高血压
  end 高血压,
  case
  when 恶性肿瘤 is null then
  0
  else
  恶性肿瘤
  end 恶性肿瘤,
  case
  when 糖尿病 is null then
  0
  else
  糖尿病
  end 糖尿病,
  case
  when 重性精神病 is null then
  0
  else
  重性精神病
  end 重性精神病
   
  from (select t3.org_id, count(t3.org_id) 高血压
  from chronic_ehr t1, chronic_event t2, chronic_org t3
  where t1.ssid = t2.ssid
  and t1.event = t2.event
  and t3.org_id = t2.org_id and (t1.catalog_code = '0510040001' or t1.catalog_code = '0510030001')
  and t1.COMMIT_TIME between to_date('2011/1/1','yyyy-mm-dd') and to_date('2012/1/1','yyyy-mm-dd') 
  group by t3.org_id
   
  ) s1
  full join (select t3.org_id, count(t3.org_id) 恶性肿瘤
  from chronic_ehr t1, chronic_event t2, chronic_org t3
  where t1.ssid = t2.ssid
  and t1.event = t2.event
  and t3.org_id = t2.org_id
  and (t1.catalog_code = '0510040005' or t1.catalog_code = '0510030005') and t1.COMMIT_TIME between to_date('2011/1/1','yyyy-mm-dd') and to_date('2012/1/1','yyyy-mm-dd') 
  group by t3.org_id
   
  ) s2 on s1.org_id = s2.org_id
  full join (select t3.org_id, count(t3.org_id) 糖尿病
  from chronic_ehr t1, chronic_event t2, chronic_org t3
  where t1.ssid = t2.ssid
  and t1.event = t2.event
  and t3.org_id = t2.org_id
  and (t1.catalog_code = '0510040002' or t1.catalog_code = '0510030002') and t1.COMMIT_TIME between to_date('2011/1/1','yyyy-mm-dd') and to_date('2012/1/1','yyyy-mm-dd') 
  group by t3.org_id
   
  ) s3 on s2.org_id = s3.org_id
  full join (select t3.org_id, count(t3.org_id) 重性精神病
  from chronic_ehr t1, chronic_event t2, chronic_org t3
  where t1.ssid = t2.ssid
  and t1.event = t2.event
  and t3.org_id = t2.org_id
  and (t1.catalog_code = '0510030007' or t1.cat