嵌套选择和连接

我使用下面的代码从其他表中计算所需的信息。我使用连接来显示名称而不是 ID，并从其他表中获取所需的总和。我曾经COALESCE将空值转换为零。

如果我需要对已经合并的值求和，我不得不再次使用它，上面的代码很难理解，而且越来越难，因为我需要添加更多信息，这只是主项目的一小部分，所以它真的会很难使用它，并且会有很多错误和错误。

有必要这么复杂吗？还是我做错了？如果它必须像这样复杂，是否有任何替代品可以用更简单的方法和代码获得相同的结果？另一个 RDBMS 或其他什么？

SELECT P.PROJ_ID, P.PROJ_STATUS, P.TYPE_ID, PT.TYPE_NAME, P.CLASS_ID, CLA.CLASS_NAME, P.PROJ_NO, P.PROJ_YEAR, P.PROJ_NAME, P.OLD_PROJ_NAME, 
P.AGENCY_ID, A.AGENCY_NAME, P.CONTRACT_NO, P.CONTRACT_DATE, P.MINISTRY_ID, M.MINISTRY_NAME, 
P.DIRECTORATE_ID, DIR.DIRECTORATE_NAME, 
P.COST, P.ESTIMATED_COST, COALESCE(CO.ADDED_COSTS, 0) AS ADDED_COSTS, (COALESCE(P.COST, 0) + COALESCE(CO.ADDED_COSTS, 0)) AS TOTAL_COST, 
P.ALLOCATION, 
COALESCE(EPY.PAST_YEARS, 0) AS PAST_YEARS, 
COALESCE(EF.PAST_MONTHS, 0) AS PAST_MONTHS, 
COALESCE(ECM.CURRENT_MONTH, 0) AS CURRENT_MONTH,
COALESCE(ECY.CURRENT_YEAR, 0) AS CURRENT_YEAR,
COALESCE(E.TOTAL_EXPENSES, 0) AS TOTAL_EXPENSES,

COALESCE(CASH_EPY.CASH_PAST_YEARS, 0) AS CASH_PAST_YEARS, 
COALESCE(CASH_EF.CASH_PAST_MONTHS, 0) AS CASH_PAST_MONTHS, 
COALESCE(CASH_ECM.CASH_CURRENT_MONTH, 0) AS CASH_CURRENT_MONTH,
COALESCE(CASH_ECY.CASH_CURRENT_YEAR, 0) AS CASH_CURRENT_YEAR,
COALESCE(CASH_E.CASH_TOTAL_EXPENSES, 0) AS CASH_TOTAL_EXPENSES,

COALESCE(TOTAL_E.TOTAL_EXPENSES_CASH, 0) AS TOTAL_EXPENSES_CASH,

((COALESCE(P.COST, 0) + COALESCE(CO.ADDED_COSTS, 0)) - COALESCE(E.TOTAL_EXPENSES, 0)) AS REMAINING,
P.DURATION, COALESCE(DU.ADDED_DURATIONS, 0) AS ADDED_DURATIONS,
(COALESCE(P.DURATION, 0) + COALESCE(DU.ADDED_DURATIONS, 0)) AS TOTAL_DURATION, P.START_DATE, P.FINISH_DATE, 
P.GOVERNORATE_ID, G.GOVERNORATE_NAME, P.PROVINCE_ID, PR.PROVINCE_NAME, P.DISTRICT_ID, D.DISTRICT_NAME, 
P.TOWN_ID, T.TOWN_NAME, 

COALESCE( (E.TOTAL_EXPENSES / (COALESCE(P.COST, 0) + COALESCE(CO.ADDED_COSTS, 0)))/100, 0) AS FINANCIAL_ACHIEVEMENT,

P.MATERIAL_ACHIEVEMENT, P.NOTES
FROM PROJECTS P

INNER JOIN PROJECTS_TYPES PT
ON P.TYPE_ID = PT.TYPE_ID

INNER JOIN CLASSES CLA
ON P.CLASS_ID = CLA.CLASS_ID

INNER JOIN AGENCIES A
ON P.AGENCY_ID = A.AGENCY_ID

LEFT JOIN MINISTRIES M
ON P.MINISTRY_ID = M.MINISTRY_ID

LEFT JOIN DIRECTORATES DIR
ON P.DIRECTORATE_ID = DIR.DIRECTORATE_ID

INNER JOIN GOVERNORATES G
ON P.GOVERNORATE_ID = G.GOVERNORATE_ID

LEFT JOIN PROVINCES PR
ON P.PROVINCE_ID = PR.PROVINCE_ID

LEFT JOIN DISTRICTS D
ON P.DISTRICT_ID = D.DISTRICT_ID

LEFT JOIN TOWNS T
ON P.TOWN_ID = T.TOWN_ID

-- ADDED COSTS
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(ADDED_VALUE, 0) - COALESCE(REMOVED_VALUE, 0)) as ADDED_COSTS
FROM COSTS
GROUP BY PROJ_ID ) AS CO
ON P.PROJ_ID = CO.PROJ_ID

-- EXPENSES FROM PAST YEARS
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as PAST_YEARS
  FROM EXPENSES WHERE EXTRACT(YEAR FROM DOC_DATE) < EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = FALSE
GROUP BY PROJ_ID ) AS EPY
ON P.PROJ_ID= EPY.PROJ_ID

-- EXPENSES FROM PAST MONTHS IN CUREENT YEAR
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as PAST_MONTHS
FROM EXPENSES WHERE EXTRACT(MONTH FROM DOC_DATE) < EXTRACT(MONTH FROM CURRENT_DATE) 
AND EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE)  AND CASH_DEDUCTIONS = FALSE
GROUP BY PROJ_ID ) AS EF
ON P.PROJ_ID= EF.PROJ_ID

-- EXPENSES FROM CURRENT MONTH AND YEAR
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CURRENT_MONTH
  FROM EXPENSES WHERE EXTRACT(MONTH FROM DOC_DATE) = EXTRACT(MONTH FROM CURRENT_DATE) AND EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = FALSE
GROUP BY PROJ_ID ) AS ECM
ON P.PROJ_ID= ECM.PROJ_ID

-- SUM OF EXPENSES IN CURRENT YEAR
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CURRENT_YEAR
  FROM EXPENSES WHERE EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = FALSE
GROUP BY PROJ_ID ) AS ECY
ON P.PROJ_ID= ECY.PROJ_ID

-- TOTAL EXPENSES FROM ALL TIME
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as TOTAL_EXPENSES
FROM EXPENSES WHERE CASH_DEDUCTIONS = FALSE
GROUP BY PROJ_ID ) AS E
ON P.PROJ_ID= E.PROJ_ID

-- CASH DEDUCTIONS SUMS
-- CASH DEDUCTIONS FROM PAST YEARS
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CASH_PAST_YEARS
  FROM EXPENSES WHERE EXTRACT(YEAR FROM DOC_DATE) < EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = TRUE
GROUP BY PROJ_ID ) AS CASH_EPY
ON P.PROJ_ID= CASH_EPY.PROJ_ID

-- CASH DEDUCTIONS FROM PAST MONTHS IN CUREENT YEAR
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CASH_PAST_MONTHS
FROM EXPENSES WHERE EXTRACT(MONTH FROM DOC_DATE) < EXTRACT(MONTH FROM CURRENT_DATE) 
AND EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE)  AND CASH_DEDUCTIONS = TRUE
GROUP BY PROJ_ID ) AS CASH_EF
ON P.PROJ_ID= CASH_EF.PROJ_ID

-- CASH DEDUCTIONS FROM CURRENT MONTH AND YEAR
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CASH_CURRENT_MONTH
  FROM EXPENSES WHERE EXTRACT(MONTH FROM DOC_DATE) = EXTRACT(MONTH FROM CURRENT_DATE) AND EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = TRUE
GROUP BY PROJ_ID ) AS CASH_ECM
ON P.PROJ_ID= CASH_ECM.PROJ_ID

-- SUM OF CASH DEDUCTIONS IN CURRENT YEAR
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CASH_CURRENT_YEAR
  FROM EXPENSES WHERE EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = TRUE
GROUP BY PROJ_ID ) AS CASH_ECY
ON P.PROJ_ID= CASH_ECY.PROJ_ID

-- TOTAL CASH DEDUCTIONS FROM ALL TIME
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CASH_TOTAL_EXPENSES
FROM EXPENSES WHERE CASH_DEDUCTIONS = TRUE
GROUP BY PROJ_ID ) AS CASH_E
ON P.PROJ_ID= CASH_E.PROJ_ID

-- TOTAL EXPENSES AND CASH DEDUCTIONS FROM ALL TIME
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as TOTAL_EXPENSES_CASH
FROM EXPENSES
GROUP BY PROJ_ID ) AS TOTAL_E
ON P.PROJ_ID= TOTAL_E.PROJ_ID

-- ADDED DURATIONS
  LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(ADDED_VALUE, 0) - COALESCE(REMOVED_VALUE, 0)) as ADDED_DURATIONS
FROM DURATIONS
GROUP BY PROJ_ID ) AS DU
ON P.PROJ_ID= DU.PROJ_ID
ORDER BY P.PROJ_YEAR, P.TYPE_ID, P.PROJ_NO