Oracle中用WHERE NOT EXISTS筛选剩余目标ID的技术咨询

Got it, let's work through this. You've got a list of duplicate IDs, and you need to use Oracle's NOT EXISTS to pull out the ones that aren't returned by your existing query. Here's how to do it right, even with 200+ IDs:

Step 1: Convert your original ID list into a queryable dataset #

First, we need to turn that comma-separated list of IDs into a proper dataset (since Oracle can't directly compare a raw string to query results). This CONNECT BY method efficiently splits the string into individual rows, preserving duplicates just like your original list:

WITH original_ids AS (
    SELECT 
        TRIM(REGEXP_SUBSTR('538693 , 538693 , 541616 , 541616 , 541616 , 541620 , 541116 , 541116 , 538639 , 538639 , 538720 , 538720 , 538720 , 541608 , 541608 , 541608 , 538675 , 538675 , 538675 , 538675', '[^,]+', 1, LEVEL)) AS id
    FROM dual
    CONNECT BY LEVEL <= REGEXP_COUNT('538693 , 538693 , 541616 , 541616 , 541616 , 541620 , 541116 , 541116 , 538639 , 538639 , 538720 , 538720 , 538720 , 541608 , 541608 , 541608 , 538675 , 538675 , 538675 , 538675', '[^,]+')
)

Note: If your IDs are numeric (not strings), wrap the TRIM in TO_NUMBER() to ensure proper type matching later.

Step 2: Combine with NOT EXISTS to filter out existing IDs #

Next, plug in your existing query (the one that returns the IDs you already have) into a second CTE, then use NOT EXISTS to find missing IDs. This preserves duplicates from your original list (e.g., if 538693 appears twice and isn't in your existing results, it will show up twice in the output):

WITH original_ids AS (
    SELECT 
        TRIM(REGEXP_SUBSTR('538693 , 538693 , 541616 , 541616 , 541616 , 541620 , 541116 , 541116 , 538639 , 538639 , 538720 , 538720 , 538720 , 541608 , 541608 , 541608 , 538675 , 538675 , 538675 , 538675', '[^,]+', 1, LEVEL)) AS id
    FROM dual
    CONNECT BY LEVEL <= REGEXP_COUNT('538693 , 538693 , 541616 , 541616 , 541616 , 541620 , 541116 , 541116 , 538639 , 538639 , 538720 , 538720 , 538720 , 541608 , 541608 , 541608 , 538675 , 538675 , 538675 , 538675', '[^,]+')
),
existing_results AS (
    -- Replace this with your actual query that returns the IDs you already found
    SELECT '538675' AS id FROM dual UNION ALL
    SELECT '538675' AS id FROM dual UNION ALL
    SELECT '538675' AS id FROM dual
)
SELECT oi.id
FROM original_ids oi
WHERE NOT EXISTS (
    SELECT 1
    FROM existing_results er
    WHERE er.id = oi.id
);

Step 3: Optional: Get unique remaining IDs #

If you don't need to keep duplicates and just want a distinct list of missing IDs, add DISTINCT to the final select:

SELECT DISTINCT oi.id
FROM original_ids oi
WHERE NOT EXISTS (
    SELECT 1
    FROM existing_results er
    WHERE er.id = oi.id
);

Key Notes #

• For 200+ IDs, this method scales well—Oracle handles the string splitting efficiently.
• Make sure the ID data types match between your original list and existing query (numeric vs. string) to avoid unexpected mismatches.
• If your existing query pulls from a table, just replace the existing_results CTE with your actual SELECT id FROM your_table WHERE ... statement.

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