您可以使用带有 exists 的半自联接

select Item, FamilyCode, 
  case when exists (
    select * from t t2
    where t2.FamilyCode = t.FamilyCode and t2.Item != t.items
  ) then 'Yes' else 'No' end [Is part of a family]
from t;

我认为这可以通过计算具有相同FamilyCode的行数来实现，如果大于1，则将其标记为家族的一部分。

with cte as (
    select * from (values
        ('ABR123', 'ABR'),
        ('ABR456', 'ABR'),
        ('BCL369', 'BCL'),
        ('BCL987', 'BCL'),
        ('AEL877', 'AEL')
    ) as x([Item], [FamilyCode])
)
select *,
    [IsPartOfFamily] =
        case when count(*) over (partition by [FamilyCode]) > 1 
        then 1
        else 0 end
from cte;