如何通过SQL查询实现指定常量键与关联表值的字符串拼接

Got it, let's tackle this problem step by step. The core idea is to map each name in table1 to your hardcoded constants, join with table2 to get the corresponding value, then concatenate everything into the desired string format.

MySQL Implementation #

The simplest approach uses a CASE statement for the constant mapping and GROUP_CONCAT to aggregate the results:

SELECT GROUP_CONCAT(
    CONCAT(
        CASE t1.name
            WHEN 'abc' THEN 6
            WHEN 'cde' THEN 8
            WHEN 'efg' THEN 5
        END,
        ':',
        t2.value
    ) ORDER BY t1.table1_id SEPARATOR ','
) AS result_string
FROM table1 t1
INNER JOIN table2 t2 ON t1.table1_id = t2.table2_id;

Breakdown:

• The CASE clause directly maps each name to its corresponding hardcoded number.
• CONCAT combines the mapped constant and value into a single string like 6:10.
• GROUP_CONCAT aggregates all these strings into one, separated by commas. The ORDER BY ensures the output matches your desired sequence (since table1_id 1,2,3 correspond to 'abc','cde','efg').

PostgreSQL Implementation #

PostgreSQL uses STRING_AGG instead of GROUP_CONCAT, and requires explicit conversion of numeric values to text:

SELECT STRING_AGG(
    CONCAT(
        CASE t1.name
            WHEN 'abc' THEN '6'
            WHEN 'cde' THEN '8'
            WHEN 'efg' THEN '5'
        END,
        ':',
        CAST(t2.value AS TEXT)
    ), ',' ORDER BY t1.table1_id
) AS result_string
FROM table1 t1
INNER JOIN table2 t2 ON t1.table1_id = t2.table2_id;

Other Databases #

For SQL Server, you'll also use STRING_AGG (similar to PostgreSQL). For Oracle, use LISTAGG with the same mapping logic. The core pattern remains consistent: map the names to constants, concatenate each pair, then aggregate into the final string.

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