多值列子查询需求：基于两张关联表生成指定输出

Got it, let's figure out how to join Table 1 and Table 2 while handling that comma-separated values column in Table 2. The goal here is to map each individual value in Table 2's values field to the corresponding description from Table 1, resulting in a row for every matched pair. Here are solutions for the most common databases:

MySQL (8.0+) #

If you're using MySQL 8.0.19 or later, you can use the built-in STRING_SPLIT function to break apart the comma-separated values:

SELECT 
    t2.FK,
    s.value AS id,
    t1.description
FROM Table2 t2
JOIN STRING_SPLIT(t2.values, ',') s
JOIN Table1 t1 ON s.value = t1.id;

For older MySQL 8.0 versions (before 8.0.19), use JSON_TABLE as a workaround:

SELECT 
    t2.FK,
    split_val.value AS id,
    t1.description
FROM Table2 t2
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(t2.values, ',', '","'), '"]'),
    '$[*]' COLUMNS (value VARCHAR(255) PATH '$')
) split_val
JOIN Table1 t1 ON split_val.value = t1.id;

PostgreSQL #

PostgreSQL uses string_to_array to convert the comma-separated string into an array, then unnest to turn array elements into rows. A clean way to do this is with a LATERAL join:

SELECT 
    t2.FK,
    s.id,
    t1.description
FROM Table2 t2
LATERAL unnest(string_to_array(t2.values, ',')) AS s(id)
JOIN Table1 t1 ON s.id = t1.id;

SQL Server #

SQL Server has a straightforward STRING_SPLIT function available in 2016 and later:

SELECT 
    t2.FK,
    s.value AS id,
    t1.description
FROM Table2 t2
JOIN STRING_SPLIT(t2.values, ',') s
JOIN Table1 t1 ON s.value = t1.id;

Key Idea #

All these solutions follow the same core logic:

1. Split the comma-separated values column in Table 2 into individual rows (one row per value).
2. Join the split rows with Table 1 using the id column to get the matching description.

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