首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > oracle >

这语句怎么优化

2012-03-05 
这语句如何优化select r2.area_id, r2.org_name,r1.org_id,sum( r1.高血压) 高血压,sum( r1.恶性肿瘤) 恶

这语句如何优化
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.catalog_code = '0510040007') 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
   
  ) s4 on s3.org_id = s4.org_id) r1 left join chronic_org r2 on r2.org_id = r1.org_id where 1=1 AND r2.AREA_ID in (select t8.area_id from chronic_area_dict t8 start with t8.area_id = '2989bc37-8a31-41aa-9f88-8b168125481f' connect by prior t8.area_id = t8.parent_code ) group by r2.area_id, r2.org_name,r1.org_id ORDER BY r2.area_id, r1.org_id

[解决办法]
我们做的公卫没有包括恶性肿瘤分类……
你的语法太长了,实际根本不可行。
改进建议:
1、s1,s2,s3,s4四类子句可以写为一个查询,也可以做成一个视图V_R1
select t3.org_id,
sum(decode(t1.catalog_code, '0510040001', 1, '0510030001', 1, 0)) as 高血压,
sum(decode(t1.catalog_code, '0510040005', 1, '0510030005', 1, 0)) as 恶性肿瘤, --后面的两类同理
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.COMMIT_TIME between to_date('2011/1/1', 'yyyy-mm-dd') and
to_date('2012/1/1', 'yyyy-mm-dd')
2、where 1=1 去掉
3、子句中的order by 没有作用,即使进行了排序,到外面还是需要进行排序,无端增加工作量
4、把这个也做成视图V_areaIdList
select t8.area_id
from chronic_area_dict t8
start with t8.area_id = '2989bc37-8a31-41aa-9f88-8b168125481f'
connect by prior t8.area_id = t8.parent_code
5、最终你的语句就是:
select r2.area_id,
r2.org_name,
r1.org_id,
sum(nvl(r1.高血压,0)) 高血压,
sum(nvl(r1.恶性肿瘤,0)) 恶性肿瘤,
sum(nvl(r1.糖尿病,0)) 糖尿病,
sum(nvl(r1.重性精神病,0)) 重性精神病
from V_R1 r1
left join chronic_org r2 on r2.org_id = r1.org_id
 where r2.AREA_ID in (select area_id from v_areaIdList)
 group by r2.area_id, r2.org_name, r1.org_id
 ORDER BY r2.area_id, r1.org_id

热点排行