左连接字段值存于列名的SQL能否通过单查询获取目标结果？

First, let's restate your source table for clarity:

Your concern makes sense at first glance—since the "types" of codes (product, brand, shop) are stored as column names instead of row values, it feels like you can't use them directly for a left join. But the fix is straightforward: you just need to unpivot those columns into rows first, which turns the column names into regular field values that you can use in your join condition.

Here's how to do it with standard SQL (works in most databases) #

Use UNION ALL to convert each code column into a separate row, along with a label for the code type:

-- Replace 'your_table' with your actual table name
-- Replace 'code_reference' with your target join table
SELECT
    base.unioncode,
    base.qt,
    code_metadata.code_type,
    code_metadata.code_value,
    ref.description -- Example field from your joined table
FROM (
    -- Unpivot each code column into rows
    SELECT unioncode, qt, 'product' AS code_type, productCode AS code_value FROM your_table
    UNION ALL
    SELECT unioncode, qt, 'brand' AS code_type, brandCode AS code_value FROM your_table
    UNION ALL
    SELECT unioncode, qt, 'shop' AS code_type, shopCode AS code_value FROM your_table
) AS base
LEFT JOIN code_reference AS ref
    ON base.code_type = ref.code_type -- Match the code type (now a row value)
    AND base.code_value = ref.code_value; -- Match the actual code number

For databases that support UNPIVOT (like SQL Server, Oracle) #

You can use the built-in UNPIVOT operator to make the code cleaner:

SELECT
    up.unioncode,
    up.qt,
    up.code_type,
    up.code_value,
    ref.description
FROM your_table
UNPIVOT (
    code_value FOR code_type IN (productCode, brandCode, shopCode)
) AS up
LEFT JOIN code_reference AS ref
    ON up.code_type = ref.code_type
    AND up.code_value = ref.code_value;

Why this works #

By unpivoting, you're transforming your wide table into a long table where each code type/value pair is a separate row. This turns the column names (like productCode) into a regular code_type field that you can use directly in your left join condition, just like any other row value.

The end result is a single SQL query that achieves exactly what you need—no need for multiple queries or extra processing.

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