基于双表规则关联第三表的SQL查询结果异常问题求助

表结构与规则 #

Approval表 #

规则：筛选Approval Date为8月的Orç记录。

FAT表 #

规则：剔除Status为Cancelado的IDFat记录。

Value表 #

说明：需基于前两张表的规则汇总Value1和Value2的值。

预期结果 #

当前查询语句 #

select 
    o.Orç,
    o.Value,
    o.Approval_Date,
    fat.Valor1,
    fat.Valor2  
from 
    (
    select
        f.Orç,
        f.Status,
        fx.VALOR1,
        fx.VALOR2
    from
        FAT as f
    left join Value as fx on
        f.IDFat = fx.IDFat
) as fat
left join Approval as o on o.Orç = fat.Orç
where
    fat.Status <> 'C'
    and o.Approval_Date >= '2024-08-01' 
    and o.Approval_Date < '2024-09-01'
 group by o.Orç

问题排查与修正 #

当前SQL存在的问题 #

1. 筛选条件不匹配：FAT表的取消状态是Cancelado，但查询用fat.Status <> 'C'无法准确匹配，且未处理Status为空的有效记录。
2. 未做数值汇总：预期结果需要对Value1/Value2求和，但当前直接取单条数据的字段值，分组后会返回随机行的数值，不符合需求。
3. 字段名不一致：Value表字段为Value1/Value2，但查询写为fx.VALOR1/fx.VALOR2，可能导致字段不存在或取值错误（取决于数据库大小写敏感性）。
4. 分组逻辑不规范：多数SQL模式下，GROUP BY需包含所有非聚合字段，仅按o.Orç分组会引发语法错误或非预期结果。
5. 关联顺序不合理：先关联FAT和Value再关联Approval，容易引入无效数据，应优先筛选符合条件的Approval记录再做关联。

修正后的SQL语句 #

SELECT
    a.ORÇ,
    a.Value,
    a.`Approval Date`,
    -- 清理数值格式并求和，再还原为原格式
    CONCAT('R$ ', FORMAT(SUM(CASE WHEN v.Value1 != 'R$ -' THEN REPLACE(REPLACE(v.Value1, 'R$ ', ''), ',', '.') ELSE 0 END), 2, 'de_DE')) AS Value1,
    CONCAT('R$ ', FORMAT(SUM(CASE WHEN v.Value2 != 'R$ -' THEN REPLACE(REPLACE(v.Value2, 'R$ ', ''), ',', '.') ELSE 0 END), 2, 'de_DE')) AS Value2
FROM
    Approval a
INNER JOIN FAT f ON a.ORÇ = f.ORÇ
INNER JOIN Value v ON f.IDFat = v.IDFat
WHERE
    -- 筛选2024年8月的审批记录
    DATE_FORMAT(STR_TO_DATE(a.`Approval Date`, '%d/%m/%Y'), '%Y-%m') = '2024-08'
    -- 排除Cancelado的FAT记录，保留空状态的有效记录
    AND (f.Status != 'Cancelado' OR f.Status IS NULL)
GROUP BY
    a.ORÇ, a.Value, a.`Approval Date`

说明 #

• 先从Approval表筛选8月数据，再关联未取消的FAT记录，最后关联Value表做数值汇总，避免无效数据干扰。
• 处理R$ -的空值情况，转换为0后求和，再格式化回原货币格式。
• 使用INNER JOIN确保仅保留符合所有规则的关联数据。

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