三表SQL按matricula分组的(total/SUM(kmfim-quilometragem))运算报错求助

你的问题主要出在两个地方：一是JOIN子句没指定连接条件，导致数据库无法判断两个子查询的关联逻辑，触发matricula歧义；二是费用汇总的子查询没有做分组求和，直接拿单条记录的total去计算，结果不符合需求。

下面是修正后的完整SQL，能实现按matricula分组计算单位里程成本（总费用/总里程）：

SELECT 
    km_data.matricula,
    -- 用NULLIF避免除数为0的报错，若总里程为0则返回NULL
    ROUND(total_data.total_geral / NULLIF(km_data.kms_totais, 0), 2) AS custo_km
FROM (
    -- 子查询1：计算每辆车的总行驶里程
    SELECT 
        matricula,
        SUM(kmfim - quilometragem) AS kms_totais
    FROM formulario
    GROUP BY matricula
) AS km_data
JOIN (
    -- 子查询2：汇总每辆车的所有费用（加油+过路费+维修）
    SELECT 
        matricula,
        SUM(total) AS total_geral
    FROM (
        SELECT matricula, abastecimento_euros AS total FROM formulario
        UNION ALL
        SELECT matricula, custo AS total FROM viaverde
        UNION ALL
        SELECT matricula, valor AS total FROM reparacoes
    ) AS all_costs
    GROUP BY matricula
) AS total_data ON km_data.matricula = total_data.matricula;

关键修正点说明 #

• 明确JOIN连接条件：两个子查询通过matricula关联，彻底解决字段歧义问题
• 费用必须分组求和：UNION ALL只是把三个表的费用记录合并，必须再按matricula分组SUM，才能得到每辆车的总费用
• 处理除数为0的情况：用NULLIF(km_data.kms_totais, 0)确保当总里程为0时，不会触发除以0的报错，而是返回NULL
• 字段命名更清晰：给子查询和字段起直观的别名（比如km_data、total_geral），避免混淆

如果需要关联原查询中的f表（比如外层的formulario），可以在最外层再JOIN，比如：

SELECT 
    f.*,
    ROUND(total_data.total_geral / NULLIF(km_data.kms_totais, 0), 2) AS custo_km
FROM formulario f
JOIN (
    SELECT 
        matricula,
        SUM(kmfim - quilometragem) AS kms_totais
    FROM formulario
    GROUP BY matricula
) AS km_data ON f.matricula = km_data.matricula
JOIN (
    SELECT 
        matricula,
        SUM(total) AS total_geral
    FROM (
        SELECT matricula, abastecimento_euros AS total FROM formulario
        UNION ALL
        SELECT matricula, custo AS total FROM viaverde
        UNION ALL
        SELECT matricula, valor AS total FROM reparacoes
    ) AS all_costs
    GROUP BY matricula
) AS total_data ON f.matricula = total_data.matricula;

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