如何用SQL GROUP BY筛选恰好包含指定设备型号列表的ID？

要筛选出仅包含指定设备型号（如['A','B']）、无其他型号的ID，需要同时满足两个核心条件：

1. 该ID包含列表中的所有型号；
2. 该ID不存在列表之外的任何型号。

方法一：通过HAVING子句组合条件 #

直接在分组后验证型号数量、是否包含所有目标型号、无额外型号：

SELECT id
FROM tbl
GROUP BY id
HAVING 
    -- 该ID的设备型号总数恰好等于指定列表的长度（2种）
    COUNT(DISTINCT device_model) = 2
    -- 确保不存在列表外的型号
    AND SUM(CASE WHEN device_model NOT IN ('A', 'B') THEN 1 ELSE 0 END) = 0
    -- 确保包含A型号
    AND SUM(CASE WHEN device_model = 'A' THEN 1 ELSE 0 END) > 0
    -- 确保包含B型号
    AND SUM(CASE WHEN device_model = 'B' THEN 1 ELSE 0 END) > 0;

方法二：子查询排除含额外型号的ID #

先找出所有包含非目标型号的ID，再从结果中排除它们，最后验证是否包含所有目标型号：

SELECT id
FROM tbl
WHERE id NOT IN (
    -- 找出所有有非A/B型号的ID
    SELECT id
    FROM tbl
    WHERE device_model NOT IN ('A', 'B')
)
GROUP BY id
-- 验证包含的型号恰好是2种（即A和B）
HAVING COUNT(DISTINCT device_model) = 2;

方法三：更简洁的条件组合 #

利用条件计数简化判断：

SELECT id
FROM tbl
GROUP BY id
HAVING
    -- 型号总数等于目标列表长度
    COUNT(DISTINCT device_model) = 2
    -- 目标列表中的型号都存在
    AND COUNT(DISTINCT CASE WHEN device_model IN ('A', 'B') THEN device_model END) = 2;

以上三种方法都能准确筛选出仅包含A和B型号的ID（如示例中的id=1），排除像id=3这种包含额外型号C的记录。

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