如何合并两条使用sum聚合函数的SQL语句 无需创建capital_cost_sum视图

可以合并，无需创建capital_cost_sum视图，只需要把视图的计算逻辑直接内嵌到查询语句中即可，最终执行结果和原逻辑完全一致。

合并后的优化SQL（子查询写法，兼容所有支持标准SQL的数据库） #

SELECT 
    capital_project.capital_project_id,
    capital_project.name AS capital_project_name,
    capital_project.year,
    capital_project.description,
    CASE
        WHEN capital_project.proforma_cost IS NOT NULL THEN capital_project.proforma_cost
        ELSE cost_sum.summed_renewal_cost
    END AS estimated_project_cost,
    capital_project.community_id
FROM capital_project
LEFT JOIN (
    SELECT 
        cpfc.capital_project_id,
        SUM(fc.renewal_cost) AS summed_renewal_cost
    FROM capital_project_feature_combination cpfc
    LEFT JOIN feature_calculation fc 
        ON cpfc.feature_id = fc.feature_id
    GROUP BY cpfc.capital_project_id
) AS cost_sum 
ON capital_project.capital_project_id = cost_sum.capital_project_id;

可选CTE写法（适配MySQL8.0+、PostgreSQL、SQL Server等支持CTE的数据库，可读性更高） #

WITH cost_sum AS (
    SELECT 
        cpfc.capital_project_id,
        SUM(fc.renewal_cost) AS summed_renewal_cost
    FROM capital_project_feature_combination cpfc
    LEFT JOIN feature_calculation fc 
        ON cpfc.feature_id = fc.feature_id
    GROUP BY cpfc.capital_project_id
)
SELECT 
    capital_project.capital_project_id,
    capital_project.name AS capital_project_name,
    capital_project.year,
    capital_project.description,
    CASE
        WHEN capital_project.proforma_cost IS NOT NULL THEN capital_project.proforma_cost
        ELSE cost_sum.summed_renewal_cost
    END AS estimated_project_cost,
    capital_project.community_id
FROM capital_project
LEFT JOIN cost_sum 
ON capital_project.capital_project_id = cost_sum.capital_project_id;

说明 #

上述写法去掉了原视图逻辑中冗余的多层子查询嵌套，执行效率和原逻辑一致，不需要额外创建视图对象。

内容的提问来源于stack exchange，提问作者Andre