这语句怎么优化
这语句如何优化
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
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