SQL查询如何关联字典表获取分类名并处理NULL值完成拼接

CONCAT()函数无法得到正确结果的核心原因：绝大多数SQL引擎中，只要CONCAT()的任意入参为NULL，函数整体返回值即为NULL。直接拼接10个分类字段时，只要存在任意空分类字段就会返回异常结果，同时原生CONCAT()也无法自动关联字典表映射分类名称、跳过空值。

整体逻辑分为三步：

• 将Data表中每行的10个分类字段（cat1~cat10）从宽表结构拆为行结构，提前过滤值为NULL的分类ID
• 关联Dictionary字典表，匹配每个分类ID对应的标准分类名称
• 按产品维度分组，将同产品下的所有分类名称拼接为逗号分隔的字符串

MySQL 8.0+ #

WITH product_cat_unpivot AS (
    SELECT product_id, product_name, cat1 AS cat_id FROM Data WHERE cat1 IS NOT NULL
    UNION ALL
    SELECT product_id, product_name, cat2 AS cat_id FROM Data WHERE cat2 IS NOT NULL
    UNION ALL
    SELECT product_id, product_name, cat3 AS cat_id FROM Data WHERE cat3 IS NOT NULL
    UNION ALL
    SELECT product_id, product_name, cat4 AS cat_id FROM Data WHERE cat4 IS NOT NULL
    UNION ALL
    SELECT product_id, product_name, cat5 AS cat_id FROM Data WHERE cat5 IS NOT NULL
    UNION ALL
    SELECT product_id, product_name, cat6 AS cat_id FROM Data WHERE cat6 IS NOT NULL
    UNION ALL
    SELECT product_id, product_name, cat7 AS cat_id FROM Data WHERE cat7 IS NOT NULL
    UNION ALL
    SELECT product_id, product_name, cat8 AS cat_id FROM Data WHERE cat8 IS NOT NULL
    UNION ALL
    SELECT product_id, product_name, cat9 AS cat_id FROM Data WHERE cat9 IS NOT NULL
    UNION ALL
    SELECT product_id, product_name, cat10 AS cat_id FROM Data WHERE cat10 IS NOT NULL
)
SELECT 
    p.product_name,
    GROUP_CONCAT(d.cat_name ORDER BY p.cat_id SEPARATOR ',') AS categories
FROM product_cat_unpivot p
LEFT JOIN Dictionary d ON p.cat_id = d.cat_id
GROUP BY p.product_id, p.product_name;

MySQL 5.x版本可直接去掉CTE定义，将UNION ALL拼接的子查询作为FROM后的数据源即可使用。

PostgreSQL #

SELECT
    d.product_name,
    STRING_AGG(dic.cat_name, ',' ORDER BY v.cat_id) AS categories
FROM Data d
JOIN LATERAL (VALUES
    (d.cat1), (d.cat2), (d.cat3), (d.cat4), (d.cat5),
    (d.cat6), (d.cat7), (d.cat8), (d.cat9), (d.cat10)
) v(cat_id) ON v.cat_id IS NOT NULL
LEFT JOIN Dictionary dic ON v.cat_id = dic.cat_id
GROUP BY d.product_id, d.product_name;

SQL Server #

SELECT
    d.product_name,
    STRING_AGG(dic.cat_name, ',') WITHIN GROUP (ORDER BY v.cat_id) AS categories
FROM Data d
CROSS APPLY (VALUES
    (d.cat1), (d.cat2), (d.cat3), (d.cat4), (d.cat5),
    (d.cat6), (d.cat7), (d.cat8), (d.cat9), (d.cat10)
) v(cat_id)
LEFT JOIN Dictionary dic ON v.cat_id = dic.cat_id
WHERE v.cat_id IS NOT NULL
GROUP BY d.product_id, d.product_name;

• 自动过滤所有值为NULL的分类字段，不会因为空值导致拼接结果异常
• 分类ID和字典表的关联逻辑统一，不需要为每个分类字段单独写关联判断
• 针对给出的示例数据（product_id=13、product_name=banana、cat1=12、cat2=32、其余cat字段为NULL），查询返回的categories值为fruits,eat，完全符合预期。

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