SQL技术问询：如何筛选无指定值的ID并保留其单行记录

Hey there, let's work through this SQL problem step by step. We need to:

1. Remove all records where the ID has any entry with Value = 100
2. Keep only one row per remaining ID (any Value is acceptable for that row)

Here are a few solid approaches depending on your database system:

PostgreSQL-Specific Solution #

This uses PostgreSQL's handy DISTINCT ON clause to quickly get one row per valid ID:

SELECT DISTINCT ON (ID) ID, Value
FROM your_table
WHERE ID NOT IN (SELECT ID FROM your_table WHERE Value = 100);

Breakdown: #

• The subquery SELECT ID FROM your_table WHERE Value = 100 grabs all IDs that have a 100 value—we exclude these with NOT IN.
• DISTINCT ON (ID) ensures we only get one row per valid ID. If you want to control which Value is picked (e.g., the smallest one), add an ORDER BY like ORDER BY ID, Value at the end.

MySQL/SQL Server Alternative #

If your database doesn't support DISTINCT ON, use GROUP BY to aggregate and pick one value per ID:

SELECT ID, MIN(Value) AS Value
FROM your_table
WHERE ID NOT IN (SELECT ID FROM your_table WHERE Value = 100)
GROUP BY ID;

Notes: #

• MIN(Value) selects the smallest value for each ID—swap it with MAX(Value) if you want the largest, or use ANY_VALUE(Value) (MySQL-only) to grab an arbitrary value without strict aggregation rules.

Database-Agnostic CTE Approach #

This works across all modern databases (PostgreSQL, MySQL, SQL Server, etc.) and gives you full control over which row to keep:

WITH valid_ids AS (
    -- First, identify IDs that NEVER have a Value of 100
    SELECT ID
    FROM your_table
    GROUP BY ID
    HAVING SUM(CASE WHEN Value = 100 THEN 1 ELSE 0 END) = 0
)
SELECT ID, Value
FROM (
    -- Assign a row number to each record within the same ID
    SELECT 
        t.ID, 
        t.Value,
        ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY (SELECT NULL)) AS rn
    FROM your_table t
    JOIN valid_ids v ON t.ID = v.ID
) filtered_rows
WHERE rn = 1; -- Keep only the first row per valid ID

How it works: #

• The valid_ids CTE checks each ID: if the count of Value=100 entries is 0, the ID is valid.
• ROW_NUMBER() numbers each row per ID. ORDER BY (SELECT NULL) picks an arbitrary row, but you can replace this with ORDER BY Value DESC or another column to choose a specific value.

All these methods will produce your desired output:

ID Value
2 200
4 200

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