如何筛选所有合同均为CONTRACT_TYPE=4且数量≥1的客户？

表数据 #

BP_NUMBER,CONTRACT_TYPE
0000123, 1
0000123, 2
0000123, 3
0000123, 4
0000124, 4
0000124, 4
0000124, 4
0000125, 4
0000126, 1
0000126, 5

需求说明 #

需要筛选出所有合同类型均为CONTRACT_TYPE=4，且该类型合同数量≥1的客户（即BP_NUMBER），也就是仅拥有一个或多个类型4合同的客户。预期结果：

0000124   --(4 occurrences of type 4)
0000125   --(1 occurrence of type 4)

尝试的无效查询及问题 #

第一个查询 #

SELECT * FROM (
SELECT BP_NUMBER, CONTRACT_TYPE, COUNT(*) OVER (PARTITION BY BP_NUMBER) CT FROM CONTRACTS
WHERE (1=1)
AND DATE = '18/10/2022'
AND CONTRACT_TYPE = 4)
WHERE CT= 1;

问题：子查询仅过滤了CONTRACT_TYPE=4的行，窗口函数统计的是该客户在过滤后数据集里的行数，CT=1只能选出恰好有1个类型4合同的客户，既无法覆盖多类型4合同的情况，也没排除同时拥有其他类型合同的客户（比如0000123有类型4但也有其他类型，会被错误统计）。

第二个查询 #

SELECT BP_NUMBER FROM CONTRACTS 
WHERE (1=1)
AND CONTRACT_TYPE = 4 
AND CONTRACT_TYPE NOT IN (SELECT CONTRACT_TYPE FROM CONTRACTS WHERE CONTRACT_TYPE != 4 GROUP BY CONTRACT_TYPE);

问题：逻辑错误，子查询返回的是所有非4的合同类型，当前行CONTRACT_TYPE=4必然不在该集合中，这个条件等于无效，根本没排除拥有其他类型合同的客户。

正确解决方案 #

方法1：分组聚合筛选 #

通过分组统计每个客户的合同类型范围，确保所有合同类型都是4：

SELECT BP_NUMBER
FROM CONTRACTS
WHERE DATE = '18/10/2022' -- 保留日期过滤条件
GROUP BY BP_NUMBER
HAVING MAX(CONTRACT_TYPE) = 4 
   AND MIN(CONTRACT_TYPE) = 4
   AND COUNT(*) >= 1;

逻辑：MAX和MIN都等于4，说明该客户所有合同类型均为4；COUNT(*)>=1确保至少有一个合同（若客户有分组数据，此条件可省略）。

方法2：使用NOT EXISTS排除其他类型客户 #

SELECT DISTINCT BP_NUMBER
FROM CONTRACTS c1
WHERE DATE = '18/10/2022'
  AND c1.CONTRACT_TYPE = 4
  AND NOT EXISTS (
    SELECT 1 
    FROM CONTRACTS c2 
    WHERE c2.BP_NUMBER = c1.BP_NUMBER
      AND c2.DATE = '18/10/2022'
      AND c2.CONTRACT_TYPE != 4
  );

逻辑：先选出有类型4合同的客户，同时排除存在其他类型合同的客户，DISTINCT确保每个客户仅返回一次。

方法3：窗口函数统计合同类型数量 #

SELECT DISTINCT BP_NUMBER
FROM (
  SELECT BP_NUMBER,
         COUNT(DISTINCT CONTRACT_TYPE) OVER (PARTITION BY BP_NUMBER) type_count,
         MAX(CONTRACT_TYPE) OVER (PARTITION BY BP_NUMBER) max_type
  FROM CONTRACTS
  WHERE DATE = '18/10/2022'
) t
WHERE type_count = 1 AND max_type = 4;

逻辑：子查询统计每个客户的不同合同类型数量及最大合同类型，当类型数量为1且最大类型是4时，说明该客户只有类型4的合同。

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