字段值跨字段存在性判断及特定表条件查询实现

Alright, let's tackle your two SQL requirements step by step with concrete examples that should fit your needs!

Depending on whether you're checking values across rows or within a single row's string field, here are two common scenarios:

Scenario 1: Check if a field's value exists in another field across the entire table #

Suppose you have a table your_table with columns target_col and reference_col. To flag if each row's target_col appears anywhere in reference_col across the table:

Using IN subquery #

SELECT 
    target_col,
    reference_col,
    CASE 
        WHEN target_col IN (SELECT reference_col FROM your_table) THEN 1
        ELSE 0
    END AS value_exists
FROM your_table;

Using EXISTS subquery (better for large datasets) #

EXISTS is often more efficient because it stops searching as soon as a match is found:

SELECT 
    t1.target_col,
    t1.reference_col,
    CASE 
        WHEN EXISTS (SELECT 1 FROM your_table t2 WHERE t2.reference_col = t1.target_col) THEN 1
        ELSE 0
    END AS value_exists
FROM your_table t1;

Scenario 2: Check if a field's value exists in a comma-separated string of another field (same row) #

If reference_col stores values as a comma-separated string (e.g., "1,3,5"), use a string function like MySQL's FIND_IN_SET:

SELECT 
    target_col,
    reference_col,
    CASE 
        WHEN FIND_IN_SET(target_col, reference_col) > 0 THEN 1
        ELSE 0
    END AS value_exists
FROM your_table;

For your table with id, parent_id, and type, we need to fetch only type=1 records where their id never appears as parent_id in any other row (ignoring parent_id=0). We'll also assign a flag value of 1 to these valid records.

Recommended query using NOT EXISTS (avoids NULL issues) #

SELECT 
    t1.id,
    t1.parent_id,
    t1.type,
    1 AS valid_flag -- Assign 1 to matching records
FROM hierarchy_table t1
WHERE t1.type = 1
AND NOT EXISTS (
    SELECT 1 
    FROM hierarchy_table t2 
    WHERE t2.parent_id = t1.id 
    AND t2.parent_id != 0
);

Alternative with NOT IN #

Note: NOT IN can behave unexpectedly if the subquery returns NULL values, so NOT EXISTS is safer:

SELECT 
    id,
    parent_id,
    type,
    1 AS valid_flag
FROM hierarchy_table
WHERE type = 1
AND id NOT IN (
    SELECT DISTINCT parent_id 
    FROM hierarchy_table 
    WHERE parent_id != 0
);

Testing with your sample data #

Looking at your example records:

• Id=1 (type=1): Has a child record (Id=2, parent_id=1) → excluded
• Id=4 (type=1): Has a child record (Id=3, parent_id=4) → excluded
• Id=6 (type=1): No records have parent_id=6 → included, with valid_flag=1

Assign to a SQL variable #

If you need to assign this result to a variable (syntax varies by database), here's how to do it in MySQL:

SET @is_valid = 0;

-- Set variable to 1 if any valid record exists
SELECT @is_valid := 1
FROM hierarchy_table t1
WHERE t1.type = 1
AND NOT EXISTS (
    SELECT 1 
    FROM hierarchy_table t2 
    WHERE t2.parent_id = t1.id 
    AND t2.parent_id != 0
)
LIMIT 1; -- Use LIMIT if you only want to set it once even if multiple valid records exist

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