SQL数据验证问题：成果关联基础设施但无匹配BIL资金分配查询异常

核心问题拆解 #

你的需求是筛选出关联了基础设施但未分配匹配BIL资金的成果，业务规则明确：只要存在匹配的资金则成果有效，所有匹配项均有效时通过验证。原SQL的问题出在分组逻辑和空值判断上：

1. 分组粒度错误：原GROUP BY包含了sc.id和sc.source_type_id，导致每个「成果-基础设施-资金」的单独组合被拆分，无法对整个成果/基础设施的资金匹配情况做整体判断；
2. HAVING子句逻辑混乱：(sc.source_type_id != i.source_type_id is null)的等价逻辑是判断「sc.source_type_id与i.source_type_id不相等」这个表达式是否为NULL（即sc为空或i.source_type_id为空），这会错误选中部分符合要求的记录（比如测试用例1）；移除空值检查后，sc.source_type_id != i.source_type_id在sc为空时返回NULL，无法选中「未分配资金」的情况（即测试用例8失败）。

修正后的SQL查询 #

以下SQL精准匹配业务规则，筛选出所有关联基础设施但无匹配资金的成果-基础设施组合：

SELECT 
    a.id AS accomp_id,
    i.id AS i_id,
    i.infrastructure_type_id,
    -- 可选：显示问题类型，区分「未分配资金」和「资金不匹配」
    CASE WHEN COUNT(sc_all.id) > 0 THEN '资金不匹配' ELSE '未分配资金' END AS issue_type
FROM accomplishment a
-- 只保留关联了基础设施的成果
JOIN infrastructure i ON a.id = i.accomp_id
-- 左连接所有资金记录，用于判断是否分配过资金
LEFT JOIN accomplishment_funding sc_all ON a.id = sc_all.accomp_id
-- 左连接仅匹配source_type的资金记录，用于判断是否有符合要求的资金
LEFT JOIN accomplishment_funding sc_match ON a.id = sc_match.accomp_id 
    AND sc_match.source_type_id = i.source_type_id
WHERE a.report_fiscal_year = 2022
-- 按成果和基础设施分组，确保每个组合只返回一次
GROUP BY a.id, i.id, i.infrastructure_type_id
-- 筛选条件：没有任何匹配的资金记录
HAVING COUNT(sc_match.id) = 0;

逻辑说明 #

1. JOIN逻辑：
   • 用JOIN infrastructure锁定需要验证的核心场景（成果关联基础设施）；
   • 两个LEFT JOIN分别获取全量资金和匹配资金，既可以判断是否分配过资金，也能精准验证是否有符合要求的匹配项；
2. 分组与筛选：
   • 按a.id和i.id分组，确保对每个成果-基础设施组合做整体判断；
   • HAVING COUNT(sc_match.id) = 0直接命中两种不符合规则的情况：完全未分配资金，或分配的资金全不匹配。

测试验证 #

• 测试用例1（有匹配资金的成果）：sc_match.id会有有效计数，不会被选中，解决原查询的失败问题；
• 测试用例8（未分配资金的成果）：sc_match.id计数为0，会被选中，解决移除空值检查后的失败问题；
• 其他测试用例：由于分组逻辑是按组合整体判断，不会出现拆分单个资金记录导致的错误。

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