如何筛选同时拥有KD和CE两种CARRIER的PROV_NBR

原Provider表数据 #

需求说明 #

需筛选出同时关联KD和CE两种CARRIER的PROV_NBR对应的所有记录，预期结果如下：

预期结果 #

问题说明 #

尝试执行以下SQL语句后，返回的是所有CARRIER为KD或CE的记录，无法满足需求：

SELECT * 
FROM Provider 
WHERE CARRIER IN ('KD', 'CE')

正确查询方案 #

以下是几种可行的SQL实现方式：

方案1：子查询+分组筛选

先通过分组找出同时拥有两种CARRIER的PROV_NBR，再关联原表获取完整记录：

SELECT p.*
FROM Provider p
WHERE p.PROV_NBR IN (
    SELECT PROV_NBR
    FROM Provider
    WHERE CARRIER IN ('KD', 'CE')
    GROUP BY PROV_NBR
    HAVING COUNT(DISTINCT CARRIER) = 2
)

方案2：自连接匹配

通过自连接，确保同一个PROV_NBR同时存在两种CARRIER的记录：

SELECT p1.*
FROM Provider p1
JOIN Provider p2 
    ON p1.PROV_NBR = p2.PROV_NBR
WHERE p1.CARRIER IN ('KD', 'CE')
  AND p2.CARRIER = CASE WHEN p1.CARRIER = 'KD' THEN 'CE' ELSE 'KD' END

方案3：窗口函数统计

利用窗口函数统计每个PROV_NBR的CARRIER种类数，再筛选符合条件的记录：

SELECT PROV_NBR, CARRIER
FROM (
    SELECT *,
           COUNT(DISTINCT CARRIER) OVER (PARTITION BY PROV_NBR) AS carrier_count
    FROM Provider
    WHERE CARRIER IN ('KD', 'CE')
) t
WHERE carrier_count = 2

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