SQL查询需求：筛选A4445费用为0且其他行费用>0的唯一ID

你的UNION ALL查询思路有个关键问题：它只是单独挑出两类符合局部条件的行，但没有确保这些行属于同一个ID，并且该ID同时满足你设定的两个核心规则：

1. 该ID下至少存在一行code = 'A4445'且Charge = 0.00
2. 该ID下所有code != 'A4445'的行，Charge必须都大于0.00

下面给你两种可行的解决方案，适配不同的数据库场景：

方案一：使用子查询筛选有效ID（兼容大部分数据库） #

这种方法先通过分组聚合筛选出符合要求的ID，再关联原表获取这些ID的所有行：

SELECT c.ID, c.`Line item`, c.Code, c.Charge
FROM claim c
INNER JOIN (
    SELECT ID
    FROM claim
    GROUP BY ID
    -- 条件1：统计该ID下存在A4445且Charge为0的行
    HAVING SUM(CASE WHEN code = 'A4445' AND Charge = 0.00 THEN 1 ELSE 0 END) > 0
    -- 条件2：确保该ID下没有非A4445且Charge<=0的行
    AND SUM(CASE WHEN code != 'A4445' AND Charge <= 0.00 THEN 1 ELSE 0 END) = 0
) valid_ids ON c.ID = valid_ids.ID
ORDER BY c.ID, c.`Line item`;

逻辑说明： #

• 子查询里的GROUP BY ID把数据按ID分组，然后用两个聚合条件筛选有效ID：
  • 第一个SUM(CASE...)统计符合A4445 + Charge=0的行数，大于0就说明该ID满足第一个规则
  • 第二个SUM(CASE...)统计不符合非A4445 + Charge>0的行数，等于0就说明所有非A4445的行都符合要求
• 最后关联原表，取出这些有效ID的所有行，就是你要的目标数据

方案二：使用窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

如果你的数据库支持窗口函数，这种写法会更直观，不需要关联子查询：

WITH claim_stats AS (
    SELECT 
        *,
        -- 标记当前ID是否存在符合要求的A4445行
        MAX(CASE WHEN code = 'A4445' AND Charge = 0.00 THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_valid_a4445,
        -- 标记当前ID是否存在不符合要求的非A4445行
        MAX(CASE WHEN code != 'A4445' AND Charge <= 0.00 THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_invalid_non_a4445
    FROM claim
)
SELECT ID, `Line item`, Code, Charge
FROM claim_stats
WHERE has_valid_a4445 = 1 AND has_invalid_non_a4445 = 0
ORDER BY ID, `Line item`;

逻辑说明： #

• 先用CTE（公用表表达式）给每一行计算两个标记：
  • has_valid_a4445为1时，说明当前行所属的ID存在A4445 + Charge=0的行
  • has_invalid_non_a4445为1时，说明当前行所属的ID存在非A4445 + Charge<=0的行
• 最后筛选出has_valid_a4445=1且has_invalid_non_a4445=0的所有行，就是符合要求的完整数据

为什么你的原查询不行？ #

举个例子，你提供的示例数据里的ID3334400，它有一行B0021的Charge是0.00，不符合规则，但你的原查询会把它的第三行（B0666 + 9.00）选出来，同时如果有其他ID的A4445 + 0.00行，也会被单独选出来，这样就会混合不属于同一个有效ID的行，达不到你要的效果。

内容的提问来源于stack exchange，提问作者That Guy XY