SQL查询：如何筛选仅关联指定类型客户的主客户

以下提供两种SQL实现方案，满足筛选所有关联客户仅为指定类型（示例为类型1）的主客户需求：

方案一：使用NOT EXISTS排除不符合条件的主客户 #

SELECT DISTINCT cr.MainCustomerId
FROM dbo.CustomerRelations cr
JOIN dbo.Customers c ON cr.RelatedCustomerId = c.CustomerId
WHERE c.CustomerType = 1
AND NOT EXISTS (
    SELECT 1
    FROM dbo.CustomerRelations cr2
    JOIN dbo.Customers c2 ON cr2.RelatedCustomerId = c2.CustomerId
    WHERE cr2.MainCustomerId = cr.MainCustomerId
    AND c2.CustomerType != 1
);

逻辑说明：先筛选出关联过类型1客户的主客户，再排除那些存在关联非类型1客户的主客户，最终结果即为所有关联客户都是类型1的主客户。

方案二：分组统计验证类型一致性 #

SELECT cr.MainCustomerId
FROM dbo.CustomerRelations cr
JOIN dbo.Customers c ON cr.RelatedCustomerId = c.CustomerId
GROUP BY cr.MainCustomerId
HAVING SUM(CASE WHEN c.CustomerType != 1 THEN 1 ELSE 0 END) = 0;

逻辑说明：按主客户ID分组，统计每个主客户关联的非类型1客户数量，数量为0的则表示该主客户所有关联客户都是类型1。

扩展：获取主客户自身信息 #

如果需要同时查询主客户的自身类型等信息，可以关联主客户的Customers记录：

SELECT DISTINCT cr.MainCustomerId, c_main.CustomerType AS MainCustomerType
FROM dbo.CustomerRelations cr
JOIN dbo.Customers c ON cr.RelatedCustomerId = c.CustomerId
JOIN dbo.Customers c_main ON cr.MainCustomerId = c_main.CustomerId
WHERE c.CustomerType = 1
AND NOT EXISTS (
    SELECT 1
    FROM dbo.CustomerRelations cr2
    JOIN dbo.Customers c2 ON cr2.RelatedCustomerId = c2.CustomerId
    WHERE cr2.MainCustomerId = cr.MainCustomerId
    AND c2.CustomerType != 1
);

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