SQL-如何查找不包含特定字段值的一组记录

这可以使用 group by 和 having 子句,条件是使Claims至少有一个 药物-X 而没有 H99 代码:

select t.*
from mytable t
inner join (
  select Claim_ID
  from mytable
  group by Claim_ID
  having count(case when Service = 'Drug-X' then 1 end) > 0
         and count(case when ICD10_Code = 'H99' then 1 end) = 0 
) as s on s.Claim_ID = t.Claim_ID

Demo here

使用窗口函数而不是自联接可能更有效。

select t.*
from (
    select *,
      count_DrugX = count(case when Service = 'Drug-X' then 1 end) over (partition by Claim_ID),
      count_H99   = count(case when ICD10_Code = 'H99' then 1 end) over (partition by Claim_ID)
    from mytable t
) t
where count_DrugX > 0
  and count_H99 = 0;

以下是一个使用 exists :

with yourData as (
    select *
    from (values
        ('123', '555', 'Injection', 'H99'),
        ('123', '555', 'Drug-X', NULL),
        ('123', '555', 'Exam',NULL),
        ('123', '555', 'Diagnosis', NULL),
        ('321', '556', 'Injection', 'H88'),
        ('321', '556', 'Drug-X', NULL),
        ('321', '556', 'Exam',  NULL),
        ('321', '556', 'Diagnosis',  NULL)
    ) as x(ClaimID, PersonID, [Service], [IDC10])
), badClaims as (
    select ClaimID
    from yourData
    where [Service] = 'Drug-X'
        and not exists (
            select *
            from yourData as t
            where yourData.ClaimID = t.ClaimID
                and IDC10 = 'H99'
        )
)
select *
from yourData
join badClaims
    on badClaims.ClaimID = yourData.ClaimID;

对其进行细分,cte-badClaims查找具有Drug-X的行项目,并且同一索赔中没有IDC10=H99的行项目。一旦找到此类索赔,我们将返回该索赔的所有行项目。