求助：MariaDB如何将单表多列值关联翻译为另一表对应字段

因为表结构无法修改，只能针对每个ptr1到ptr25字段单独处理，下面提供两种可行的查询写法：

方法1：多次LEFT JOIN（性能更优） #

通过给T2表起不同别名，逐个关联每个ptr字段，同时过滤掉0值的情况，直接得到对应name：

SELECT
    t1.id,
    COALESCE(t2_1.name, NULL) AS ptr1_name,
    COALESCE(t2_2.name, NULL) AS ptr2_name,
    COALESCE(t2_3.name, NULL) AS ptr3_name,
    COALESCE(t2_4.name, NULL) AS ptr4_name,
    COALESCE(t2_5.name, NULL) AS ptr5_name,
    COALESCE(t2_6.name, NULL) AS ptr6_name,
    -- 按此格式继续写出ptr7到ptr24的字段
    COALESCE(t2_25.name, NULL) AS ptr25_name
FROM T1 t1
LEFT JOIN T2 t2_1 ON t1.ptr1 = t2_1.number AND t1.ptr1 != 0
LEFT JOIN T2 t2_2 ON t1.ptr2 = t2_2.number AND t1.ptr2 != 0
LEFT JOIN T2 t2_3 ON t1.ptr3 = t2_3.number AND t1.ptr3 != 0
LEFT JOIN T2 t2_4 ON t1.ptr4 = t2_4.number AND t1.ptr4 != 0
LEFT JOIN T2 t2_5 ON t1.ptr5 = t2_5.number AND t1.ptr5 != 0
LEFT JOIN T2 t2_6 ON t1.ptr6 = t2_6.number AND t1.ptr6 != 0
-- 按此格式继续写出ptr7到ptr24的JOIN语句
LEFT JOIN T2 t2_25 ON t1.ptr25 = t2_25.number AND t1.ptr25 != 0
WHERE t1.id = 1;

说明 #

• 每个LEFT JOIN对应一个ptr字段，AND t1.ptrX != 0确保0值不会关联到T2，此时对应name字段自然为NULL，COALESCE只是让逻辑更清晰，也可以直接写t2_X.name。
• 用t2_1到t2_25的别名区分不同的T2关联实例，避免字段冲突。

方法2：子查询方式（写法更简洁） #

如果觉得多次JOIN太繁琐，也可以用子查询逐个匹配每个ptr字段的name：

SELECT
    t1.id,
    (SELECT name FROM T2 WHERE number = t1.ptr1 AND t1.ptr1 != 0) AS ptr1_name,
    (SELECT name FROM T2 WHERE number = t1.ptr2 AND t1.ptr2 != 0) AS ptr2_name,
    (SELECT name FROM T2 WHERE number = t1.ptr3 AND t1.ptr3 != 0) AS ptr3_name,
    (SELECT name FROM T2 WHERE number = t1.ptr4 AND t1.ptr4 != 0) AS ptr4_name,
    (SELECT name FROM T2 WHERE number = t1.ptr5 AND t1.ptr5 != 0) AS ptr5_name,
    (SELECT name FROM T2 WHERE number = t1.ptr6 AND t1.ptr6 != 0) AS ptr6_name,
    -- 按此格式继续写出ptr7到ptr24的子查询
    (SELECT name FROM T2 WHERE number = t1.ptr25 AND t1.ptr25 != 0) AS ptr25_name
FROM T1 t1
WHERE t1.id = 1;

说明 #

• 每个子查询针对单个ptr字段，当ptr值为0或T2中无匹配时，子查询返回NULL，正好符合需求。

预期结果 #

针对你的测试数据，执行查询后id=1的记录会输出：

id  ptr1_name  ptr2_name  ptr3_name  ptr4_name  ptr5_name  ptr6_name ... ptr25_name
1   flower     bee        NULL       flower     tree       bee       ... NULL

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