SQL查询求助：筛选拥有设备类型1/2/3且无4/5/6的用户

嘿，我来帮你搞定这个SQL查询需求！咱们先把需求拆得明明白白：
我们要找的是同时符合两个条件的用户：

• 至少拥有一台类型为1、2或3的设备
• 完全没有类型为4、5或6的设备

下面给你两种常用且易懂的写法，你可以根据自己的习惯选择：

方法1：GROUP BY + HAVING（直观易懂，适合新手） #

这种方法通过分组统计每个用户的设备类型情况，直接筛选符合条件的用户：

SELECT UserID
FROM Device
GROUP BY UserID
HAVING 
    -- 条件1：至少有一台设备是1/2/3类型
    COUNT(CASE WHEN Type IN (1,2,3) THEN 1 END) > 0
    AND 
    -- 条件2：完全没有4/5/6类型的设备
    COUNT(CASE WHEN Type IN (4,5,6) THEN 1 END) = 0;

逻辑解释： #

• COUNT(CASE WHEN Type IN (1,2,3) THEN 1 END) 会统计该用户拥有的1/2/3类型设备数量，大于0就说明至少有一台
• COUNT(CASE WHEN Type IN (4,5,6) THEN 1 END) = 0 说明该用户没有任何4/5/6类型的设备

你也可以用MAX()函数来写，逻辑是一样的：

SELECT UserID
FROM Device
GROUP BY UserID
HAVING 
    MAX(CASE WHEN Type IN (1,2,3) THEN 1 ELSE 0 END) = 1
    AND 
    MAX(CASE WHEN Type IN (4,5,6) THEN 1 ELSE 0 END) = 0;

方法2：NOT EXISTS子查询（性能友好，逻辑清晰） #

这种写法先找到有1/2/3设备的用户，再排除掉那些同时有4/5/6设备的用户：

SELECT DISTINCT UserID
FROM Device d1
WHERE 
    Type IN (1,2,3)
    AND NOT EXISTS (
        SELECT 1 
        FROM Device d2 
        WHERE d2.UserID = d1.UserID 
        AND d2.Type IN (4,5,6)
    );

逻辑解释： #

• SELECT DISTINCT UserID 是因为一个用户可能有多台1/2/3类型的设备，去重后得到唯一的用户ID
• NOT EXISTS 子查询会检查当前用户是否存在4/5/6类型的设备，如果存在就排除这个用户

扩展：关联用户表获取更多信息 #

如果你需要从用户表（User）中获取用户的其他信息（比如用户名等），可以把上面的查询结果和用户表关联：

-- 以GROUP BY方法为例
SELECT u.*
FROM User u
JOIN (
    SELECT UserID
    FROM Device
    GROUP BY UserID
    HAVING 
        COUNT(CASE WHEN Type IN (1,2,3) THEN 1 END) > 0
        AND 
        COUNT(CASE WHEN Type IN (4,5,6) THEN 1 END) = 0
) filtered_users ON u.ID = filtered_users.UserID;

小提示： #

• 如果你的设备表中UserID字段有索引，NOT EXISTS的写法通常性能会更好
• 两种写法都能实现需求，选你看着更顺眼的就行 😊

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