如何拆分表中VALUES字段并编写SQL筛选未包含的ID

Got it, let's work through this problem step by step. Your goal is to find the IDs that aren't present in the comma-separated values field of the row where email is 'c@c' (and based on your expected result, we should also exclude the target row's own ID, since 3 is the ID for 'c@c' and isn't in your desired output).

Why Your Original Query Didn't Work #

Your initial attempt used NOT LIKE, which is designed for string pattern matching—not checking if a numeric ID exists in a list of values. This approach won't correctly split the comma-separated string into individual IDs to compare against.

Step-by-Step Solution #

We need to:

1. Fetch the target row (email='c@c') to get its ID and comma-separated values.
2. Split that values string into individual numeric entries.
3. Select all IDs from the table that are neither the target row's ID nor in the split values list.

Here are examples for common SQL dialects:

MySQL (8.0.19+)

Uses STRING_SPLIT to split the values:

WITH target_row AS (
    SELECT id AS target_id, `values` AS target_values
    FROM your_table
    WHERE email = 'c@c'
),
split_values AS (
    SELECT CAST(TRIM(value) AS UNSIGNED) AS val
    FROM target_row,
         STRING_SPLIT(target_values, ',')
)
SELECT id
FROM your_table
WHERE id != (SELECT target_id FROM target_row)
  AND id NOT IN (SELECT val FROM split_values);

MySQL (Pre-8.0.19)

Uses JSON_TABLE as a workaround for older versions:

WITH target_row AS (
    SELECT id AS target_id, `values` AS target_values
    FROM your_table
    WHERE email = 'c@c'
),
split_values AS (
    SELECT CAST(val AS UNSIGNED) AS val
    FROM target_row,
         JSON_TABLE(
             CONCAT('["', REPLACE(target_values, ',', '","'), '"]'),
             '$[*]' COLUMNS(val VARCHAR(255) PATH '$')
         ) AS jt
)
SELECT id
FROM your_table
WHERE id != (SELECT target_id FROM target_row)
  AND id NOT IN (SELECT val FROM split_values);

PostgreSQL

Uses STRING_TO_ARRAY and UNNEST to split the string:

WITH target_row AS (
    SELECT id AS target_id, values AS target_values
    FROM your_table
    WHERE email = 'c@c'
),
split_values AS (
    SELECT UNNEST(STRING_TO_ARRAY(target_values, ','))::INT AS val
    FROM target_row
)
SELECT id
FROM your_table
WHERE id != (SELECT target_id FROM target_row)
  AND id NOT IN (SELECT val FROM split_values);

SQL Server

Uses STRING_SPLIT with CROSS APPLY:

WITH target_row AS (
    SELECT id AS target_id, values AS target_values
    FROM your_table
    WHERE email = 'c@c'
),
split_values AS (
    SELECT CAST(value AS INT) AS val
    FROM target_row
    CROSS APPLY STRING_SPLIT(target_values, ',')
)
SELECT id
FROM your_table
WHERE id != (SELECT target_id FROM target_row)
  AND id NOT IN (SELECT val FROM split_values);

Notes #

• The TRIM() function cleans up any accidental spaces in the comma-separated values (like '2, 3' instead of '2,3').
• If you don't need to exclude the target row's own ID, just remove the id != (SELECT target_id FROM target_row) condition.
• Replace your_table with the actual name of your table.

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