SELECT SQL求助：同ID含deactivate时所有value1-5置空

Got it, let's work through this problem step by step. The core requirement is: if any record under an ID has a type='deactivate', all value1 to value5 fields for that entire ID should be set to NULL—no matter how pid changes. Here are two straightforward, efficient ways to implement this:

Method 1: Using EXISTS Subquery #

This approach directly checks for the presence of a 'deactivate' record for each ID as we build the result set:

SELECT
    id,
    pid,
    type,
    -- Check if the ID has a deactivate record, then nullify value1
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM your_table t2
            WHERE t2.id = t1.id
              AND t2.type = 'deactivate'
        ) THEN NULL ELSE value1
    END AS value1,
    -- Repeat the same logic for value2 to value5
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM your_table t2
            WHERE t2.id = t1.id
              AND t2.type = 'deactivate'
        ) THEN NULL ELSE value2
    END AS value2,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM your_table t2
            WHERE t2.id = t1.id
              AND t2.type = 'deactivate'
        ) THEN NULL ELSE value3
    END AS value3,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM your_table t2
            WHERE t2.id = t1.id
              AND t2.type = 'deactivate'
        ) THEN NULL ELSE value4
    END AS value4,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM your_table t2
            WHERE t2.id = t1.id
              AND t2.type = 'deactivate'
        ) THEN NULL ELSE value5
    END AS value5
FROM your_table t1;

How this works: #

For every record in your_table, we run a small subquery to check if the same ID has any type='deactivate' entry. If yes, we return NULL for the value fields; if not, we keep the original value. This ensures all records for the target ID get their values nullified, regardless of pid changes.

Method 2: Using CTE for Better Performance #

If your table is large, repeating the EXISTS subquery 5 times can add unnecessary overhead. Instead, we first collect all IDs that have a 'deactivate' record, then join this list back to the main table:

-- First, get all unique IDs with at least one deactivate record
WITH deactivate_ids AS (
    SELECT DISTINCT id
    FROM your_table
    WHERE type = 'deactivate'
)
SELECT
    t1.id,
    t1.pid,
    t1.type,
    -- If the ID is in deactivate_ids, nullify the value; else keep it
    CASE WHEN d.id IS NOT NULL THEN NULL ELSE t1.value1 END AS value1,
    CASE WHEN d.id IS NOT NULL THEN NULL ELSE t1.value2 END AS value2,
    CASE WHEN d.id IS NOT NULL THEN NULL ELSE t1.value3 END AS value3,
    CASE WHEN d.id IS NOT NULL THEN NULL ELSE t1.value4 END AS value4,
    CASE WHEN d.id IS NOT NULL THEN NULL ELSE t1.value5 END AS value5
FROM your_table t1
LEFT JOIN deactivate_ids d ON t1.id = d.id;

Why this is better: #

The CTE runs once to fetch all relevant IDs, then the left join lets us quickly flag which records need their values nullified. This cuts down on redundant subquery calls, making it much faster for large datasets.

Both methods will ensure that IDs 1 and 3 (as mentioned in your requirement) have all value1 to value5 set to NULL, while other IDs retain their original values even when pid changes.

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