SELECT T.*,V.*,T.YEAR_BASIS_BAK as 更新前的值,CASE WHEN V.BEFORE_YEAR_VALUE = 0 THEN 0 WHEN V.BEFORE_YEAR_VALUE IS NULL THEN 0 ELSE (T.KPI_VALUE - V.BEFORE_YEAR_VALUE) / V.BEFORE_YEAR_VALUE END AS 更新后的值 FROM ( SELECT B.KPI_VALUE BEFORE_YEAR_VALUE, B.ORG_ID, B.KPI_ID, B.PROJECT_CATEGORY_ID FROM DM_TZJH_WCL B WHERE B.CY_ID = '201304' ) V,(select * from DM_TZJH_WCL WHERE CY_ID = '201404') T WHERE T.ORG_ID = V.ORG_ID (+) AND T.KPI_ID = V.KPI_ID (+) AND T.PROJECT_CATEGORY_ID = V.PROJECT_CATEGORY_ID (+)
2、就算左连接也不行,左联出来的结果中,无对应值的记录左连接得到的记录中,BEFORE_YEAR_VALUE值为空,也不能达到更新的目的
WHEN V.BEFORE_YEAR_VALUE = 0 THEN
0
WHEN V.BEFORE_YEAR_VALUE IS NULL THEN
0
ELSE
(T.KPI_VALUE - V.BEFORE_YEAR_VALUE) / V.BEFORE_YEAR_VALUE
END AS 更新后的值
FROM (
SELECT B.KPI_VALUE BEFORE_YEAR_VALUE,
B.ORG_ID,
B.KPI_ID,
B.PROJECT_CATEGORY_ID
FROM DM_TZJH_WCL B
WHERE B.CY_ID = '201304'
) V,(select * from DM_TZJH_WCL WHERE CY_ID = '201404') T
WHERE T.ORG_ID = V.ORG_ID (+)
AND T.KPI_ID = V.KPI_ID (+)
AND T.PROJECT_CATEGORY_ID = V.PROJECT_CATEGORY_ID (+)