获取参考列值并关联两表查询指定参考项状态

Got it, let's break down your requirements into two actionable parts and share SQL solutions that should work for your scenario. I'll base this on PostgreSQL since you mentioned character varying (adjustments can be made for other databases like MySQL/SQL Server if needed).

Your reference column stores comma-separated values (like "12,14,18") or is empty in some rows. To pull out individual numeric values from this column, you'll need to split the string and unnest the resulting array.

Here's a query that does this, while handling empty/null reference values to avoid errors:

SELECT
    t1.ID,
    t1.Name,
    -- Convert split string parts to integers for numeric handling
    CAST(split_ref AS INTEGER) AS reference_value,
    t1.status
FROM
    Table1 t1
LEFT JOIN LATERAL (
    -- Split the reference string into individual elements
    SELECT UNNEST(STRING_TO_ARRAY(t1.reference, ',')) AS split_ref
    WHERE t1.reference IS NOT NULL AND t1.reference != ''
) AS split_refs ON TRUE
ORDER BY t1.ID;

This will return each reference value as a separate row, paired with the original Table1 row's details. For rows where reference is empty (like ID 11), it won't generate split rows—you can adjust the WHERE clause in the subquery if you want to include those rows with a NULL reference value.

Assuming Table2 has a column (let's call it ref_id) that maps directly to the individual reference values in Table1's reference column, we can combine the string splitting logic with a join to Table2, then filter for your target reference items.

Here's the query:

SELECT
    t1.ID AS table1_id,
    t1.Name,
    t2.ID AS table2_id, -- Replace with your actual Table2 column name
    CAST(split_refs.split_ref AS INTEGER) AS reference_value,
    t1.status
FROM
    Table1 t1
LEFT JOIN LATERAL (
    SELECT UNNEST(STRING_TO_ARRAY(t1.reference, ',')) AS split_ref
    WHERE t1.reference IS NOT NULL AND t1.reference != ''
) AS split_refs ON TRUE
-- Join to Table2 using the split reference value
JOIN Table2 t2
    ON CAST(split_refs.split_ref AS INTEGER) = t2.ref_id -- Adjust Table2's column name as needed
-- Filter for your specific reference items
WHERE CAST(split_refs.split_ref AS INTEGER) IN (12, 14, 18, 19)
ORDER BY t1.ID;

Quick Notes: #

• If Table2 uses a different column name for the reference mapping (not ref_id), swap that out with your actual column name.
• If you want to include rows from Table1 that have no matching references in Table2 (or empty reference values), switch the JOIN Table2 to a LEFT JOIN Table2.
• The CAST to INTEGER ensures we're matching numeric values correctly, avoiding string comparison bugs.

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