Left Join求和异常：如何解决Group2未按ORCAMENTO分组的问题？

问题根源 #

你的Grupo2子查询存在两个关键问题：

1. 未关联外层查询的o.ORCAMENTO，导致计算的是全局所有ox.tr=2的记录总和，而非每个ORCAMENTO对应的分组求和；
2. 子查询内部重复使用表别名o，和外层的orc01表别名冲突，引发字段歧义。

另外Faturado字段的计算逻辑有误，除法优先级高于加法，必须给求和整体加括号才能得到正确的占比结果。

修正后的SQL代码 #

select 
    o.ORCAMENTO as "AP",
    o.VALOR as "Valor",
    IFNULL(SUM(fx.VALOR1), 0) + IFNULL(SUM(fx.VALOR2), 0) AS "Faturamento",
    -- 给求和部分加括号，确保先求和再做除法
    (IFNULL(SUM(fx.VALOR1), 0) + IFNULL(SUM(fx.VALOR2), 0)) / o.VALOR as "Faturado",
    (o.VALOR - (IFNULL(SUM(fx.VALOR1), 0) + IFNULL(SUM(fx.VALOR2), 0))) as "Pendente",
    -- 修改子查询：关联外层的ORCAMENTO，并重命名子查询内的表别名避免冲突
    (select SUM(ox.valor) 
        from orc01x as ox 
        where ox.orcamento = o.ORCAMENTO  -- 关联当前分组的ORCAMENTO
          and ox.tr = 2) as "Grupo2",
    group_concat(distinct f.DOCUMENTO) as "Nota",
    group_concat(distinct f.SITUACAO) as "Status"
from fat02 as f 
left join orc01 as o on o.ORCAMENTO = substring(f.REFERENCIA,4,5)
join fat02x as fx on fx.FATURA = f.DOCUMENTO and substring(fx.REFERENCIA,4,5) = o.ORCAMENTO
where year(o.DATA_APROV) = '2024'
    and f.DOCUMENTO NOT IN (
        SELECT f1.DOCUMENTO 
        FROM fat02 AS f1 
        WHERE f1.SITUACAO = 'C'
    )
group by o.ORCAMENTO

关键修改说明 #

1. Grupo2子查询修正：
   • 移除子查询内多余的left join orc01（已通过ox.orcamento = o.ORCAMENTO关联外层分组字段，无需重复关联）；
   • 添加ox.orcamento = o.ORCAMENTO条件，确保子查询只计算当前分组对应的orc01x记录总和；
2. Faturado字段修正：给求和部分整体加括号，保证先计算总开票金额，再除以预算金额得到占比，避免优先级导致的逻辑错误。

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