select orgName,planId,
(select plan_name from DRGS_INDEX_ESTIMATE_PLAN where plan_id=planId and rownum=1) as planName,
rank() over (ORDER BY total desc) as totalRanking,
total,
aAll,
eAll,
cAll,
qAll,
a002IndexScore,
a002IndexWeight,
a002IndexValue,
e002IndexScore,
e002IndexWeight,
e002IndexValue,
e001IndexScore,
e001IndexWeight,
e001IndexValue,
q001IndexScore,
q001IndexWeight,
q001IndexValue
from (
select
ORG_NAME as orgName,
PLAN_ID as planId,
ROUND(avg(TOTAL),2) as total,
ROUND(avg(A_ALL),2) as aAll,
ROUND(avg(E_ALL),2) as eAll,
ROUND(avg(C_ALL),2) as cAll,
ROUND(avg(Q_ALL),2) as qAll,
ROUND(avg(A002_INDEX_SCORE),2) as a002IndexScore,
ROUND(avg(A002_INDEX_WEIGHT),2) as a002IndexWeight,
ROUND(avg(A002_INDEX_VALUE),2) as a002IndexValue,
ROUND(avg(E002_INDEX_SCORE),2) as e002IndexScore,
ROUND(avg(E002_INDEX_WEIGHT),2) as e002IndexWeight,
ROUND(avg(E002_INDEX_VALUE),2) as e002IndexValue,
ROUND(avg(E001_INDEX_SCORE),2) as e001IndexScore,
ROUND(avg(E001_INDEX_WEIGHT),2) as e001IndexWeight,
ROUND(avg(E001_INDEX_VALUE),2) as e001IndexValue,
ROUND(avg(Q001_INDEX_SCORE),2) as q001IndexScore,
ROUND(avg(Q001_INDEX_WEIGHT),2) as q001IndexWeight,
ROUND(avg(Q001_INDEX_VALUE),2) as q001IndexValue
from (
select
(select area_name from DRGS_BUILT_AREA where AREA_CODE =
(select AREA_CODE from UP_ORG_UNIT where ID=ORG_ID)
and a_level = '2' and rownum = 1) as ORG_NAME,
PLAN_ID,
TOTAL,
A_ALL,
E_ALL,
C_ALL,
Q_ALL,
A002_INDEX_SCORE,
A002_INDEX_WEIGHT,
A002_INDEX_VALUE,
E002_INDEX_SCORE,
E002_INDEX_WEIGHT,
E002_INDEX_VALUE,
E001_INDEX_SCORE,
E001_INDEX_WEIGHT,
E001_INDEX_VALUE,
Q001_INDEX_SCORE,
Q001_INDEX_WEIGHT,
Q001_INDEX_VALUE
from v_index_esti_score_detail where plan_id='4028468162e1221b0162e1b19ad100d0' and ORG_DIM = '10')
where ORG_NAME is not null
group by ORG_NAME,PLAN_ID)
select orgName,planId,
(select plan_name from DRGS_INDEX_ESTIMATE_PLAN where plan_id=planId and rownum=1) as planName,
rank() over (ORDER BY total desc) as totalRanking,
total,
aAll,
eAll,
cAll,
qAll,
a002IndexScore,
a002IndexWeight,
a002IndexValue,
e002IndexScore,
e002IndexWeight,
e002IndexValue,
e001IndexScore,
e001IndexWeight,
e001IndexValue,
q001IndexScore,
q001IndexWeight,
q001IndexValue
from (
select
ORG_NAME as orgName,
PLAN_ID as planId,
ROUND(avg(TOTAL),2) as total,
ROUND(avg(A_ALL),2) as aAll,
ROUND(avg(E_ALL),2) as eAll,
ROUND(avg(C_ALL),2) as cAll,
ROUND(avg(Q_ALL),2) as qAll,
ROUND(avg(A002_INDEX_SCORE),2) as a002IndexScore,
ROUND(avg(A002_INDEX_WEIGHT),2) as a002IndexWeight,
ROUND(avg(A002_INDEX_VALUE),2) as a002IndexValue,
ROUND(avg(E002_INDEX_SCORE),2) as e002IndexScore,
ROUND(avg(E002_INDEX_WEIGHT),2) as e002IndexWeight,
ROUND(avg(E002_INDEX_VALUE),2) as e002IndexValue,
ROUND(avg(E001_INDEX_SCORE),2) as e001IndexScore,
ROUND(avg(E001_INDEX_WEIGHT),2) as e001IndexWeight,
ROUND(avg(E001_INDEX_VALUE),2) as e001IndexValue,
ROUND(avg(Q001_INDEX_SCORE),2) as q001IndexScore,
ROUND(avg(Q001_INDEX_WEIGHT),2) as q001IndexWeight,
ROUND(avg(Q001_INDEX_VALUE),2) as q001IndexValue
from (
select
c.area_name as ORG_NAME,
PLAN_ID,
TOTAL,
A_ALL,
E_ALL,
C_ALL,
Q_ALL,
A002_INDEX_SCORE,
A002_INDEX_WEIGHT,
A002_INDEX_VALUE,
E002_INDEX_SCORE,
E002_INDEX_WEIGHT,
E002_INDEX_VALUE,
E001_INDEX_SCORE,
E001_INDEX_WEIGHT,
E001_INDEX_VALUE,
Q001_INDEX_SCORE,
Q001_INDEX_WEIGHT,
Q001_INDEX_VALUE
from v_index_esti_score_detail a,UP_ORG_UNIT b,DRGS_BUILT_AREA c where a.ORG_ID=b.id and b.AREA_CODE=c.AREA_CODE and c.a_level='2' and plan_id='4028468162e1221b0162e1b19ad100d0' and ORG_DIM = '10')
where ORG_NAME is not null
group by ORG_NAME,PLAN_ID)
uj5u.com熱心網友回復：
看耗時那欄，差不多哦uj5u.com熱心網友回復：
第1個SQL效率高些~uj5u.com熱心網友回復：
如果能執行出來的話，建議使用dbms_xplan.display_cursor比較下邏輯讀（當然這個用sqlplus的autotrace也可以獲取到），并看下是否還存在一些瓶頸，是否還能被優化？僅僅一個F5的資訊太少了，重要的謂詞資訊都沒有轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/62132.html
標籤：開發
下一篇：實體已創建。DIM-00019:創建服務時出錯 O/S-Error:（OS1053）服務沒有及時回應啟動或控制請求