如何修改SQL查询以同时适配重复ID与非重复ID场景

Got it, let's sort out this query for you. Your current statement handles duplicate IDs okay, but it's accidentally filtering out single-occurrence IDs that have NULL in Col_2—because the HAVING clause kicks out any group where there are no non-NULL values.

The key here is to first figure out how many times each ID appears in the table, then apply your two rules based on that count. We can use a window function to calculate the occurrence count for each ID without grouping the rows (which would collapse them, something we don't want since we need to keep individual valid records).

Here's the corrected query:

SELECT id, col_2
FROM (
    SELECT 
        id, 
        col_2,
        COUNT(*) OVER (PARTITION BY id) AS id_occurrences
    FROM mytable
) AS subquery
WHERE 
    -- For duplicate IDs: keep only non-NULL Col_2 records
    (id_occurrences > 1 AND col_2 IS NOT NULL)
    -- For non-duplicate IDs: keep all records regardless of Col_2
    OR id_occurrences = 1;

How this works: #

• The subquery uses COUNT(*) OVER (PARTITION BY id) to add a column id_occurrences that tells us how many times each ID appears in the table. This doesn't group or collapse rows—each original row keeps its data, plus the count for its ID.
• The outer WHERE clause applies your rules directly:
  • If the ID appears more than once, we only retain rows where Col_2 isn't NULL.
  • If the ID shows up exactly once, we keep that row no matter what Col_2 is (even if it's NULL).

This will return exactly the result set you're expecting:

ID | Col_2
A | 'ABC'
A | 'GHI'
B | 'HJH'
B | 'NBN'
C | null

Your original query used GROUP BY which collapsed rows, and the HAVING clause didn't account for single-occurrence IDs with NULL values. This window function approach preserves individual rows while letting us apply the conditional logic you need.

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