SQL需求：统计仅出现在'blue'和'red'分类的唯一设备数量

Hey there! Let's work through how to get that count of devices that only show up in the 'blue' and 'red' categories—no other categories allowed.

Core Idea #

The key here is to exclude any device that appears in a category outside of 'blue' or 'red', then count the remaining unique devices. We also want to make sure we only count devices that actually belong to at least one of 'blue' or 'red' (not devices with no category assignments at all).

Approach 1: Using NOT EXISTS to Filter Out Invalid Devices #

This method uses a subquery to check if a device has any entries in non-target categories—if it does, we skip it. We also add a check to ensure the device is part of at least one of our desired categories.

SELECT COUNT(DISTINCT device_id) AS unique_device_count
FROM your_device_category_table t1
WHERE NOT EXISTS (
    -- Subquery identifies devices with any non-blue/red category entry
    SELECT 1
    FROM your_device_category_table t2
    WHERE t2.device_id = t1.device_id
      AND t2.category NOT IN ('blue', 'red')
)
AND EXISTS (
    -- Ensure the device is in at least one of our target categories
    SELECT 1
    FROM your_device_category_table t3
    WHERE t3.device_id = t1.device_id
      AND t3.category IN ('blue', 'red')
);

Approach 2: Grouping and Aggregating to Validate Category Assignments #

Here, we group devices by their ID, then use aggregate functions to verify two things: the device has no entries in invalid categories, and it has at least one entry in a valid category.

SELECT COUNT(DISTINCT device_id) AS unique_device_count
FROM (
    SELECT device_id
    FROM your_device_category_table
    GROUP BY device_id
    HAVING 
        -- No entries in categories outside blue/red
        SUM(CASE WHEN category NOT IN ('blue', 'red') THEN 1 ELSE 0 END) = 0
        -- At least one entry in blue or red
        AND SUM(CASE WHEN category IN ('blue', 'red') THEN 1 ELSE 0 END) > 0
) AS valid_devices;

Why These Work #

Both methods will correctly filter out devices that have even one entry in a category like 'green' or 'yellow'. For your example, devices 1, 2, and 5 will pass the checks because none of their category assignments fall outside 'blue'/'red', while any device with a non-target category gets excluded entirely.

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