MySQL中如何对多字段关联同一张参考表实现映射值查询

安全阿岳

2026-9-27

解决方案

你可以选择以下两种更简洁高效的实现方式，避免多次JOIN操作：

方案1：关联子查询（适合answer列数中等场景）

直接为每个answer列配置关联子查询，语法简单易维护，无需多次写JOIN逻辑：

SELECT 
  name,
  (SELECT answer FROM answers_ref WHERE id = t1.answer1) AS answer1,
  (SELECT answer FROM answers_ref WHERE id = t1.answer2) AS answer2,
  (SELECT answer FROM answers_ref WHERE id = t1.answer3) AS answer3
FROM response t1
WHERE name = 'james foo';

如果answers_ref的`id`字段是主键或建有唯一索引，该子查询的执行效率非常高，即使有10个answer列，也只要对应加10行简单的子查询语句即可。

方案2：逆透视+单次关联+透视（适合answer列数极多场景）

如果answer列数量非常多（比如10列以上），可以先把多列answer值转成行级数据，只关联一次answers_ref后再转回列结构，全程仅需1次关联操作，性能优势更明显：
以下是通用SQL实现示例：

SELECT 
  name,
  MAX(CASE WHEN col = 'answer1' THEN answer END) AS answer1,
  MAX(CASE WHEN col = 'answer2' THEN answer END) AS answer2,
  MAX(CASE WHEN col = 'answer3' THEN answer END) AS answer3
FROM (
  -- 逆透视：将一行的多个answer列转为多行的「列名-答案id」结构
  SELECT 
    t.name,
    v.col,
    a.answer
  FROM response t
  CROSS JOIN (
    SELECT 'answer1' AS col, answer1 AS ans_id FROM response WHERE name = t.name
    UNION ALL
    SELECT 'answer2' AS col, answer2 AS ans_id FROM response WHERE name = t.name
    UNION ALL
    SELECT 'answer3' AS col, answer3 AS ans_id FROM response WHERE name = t.name
  ) v
  -- 仅单次关联答案参考表
  LEFT JOIN answers_ref a ON v.ans_id = a.id
  WHERE t.name = 'james foo'
) tmp
GROUP BY name;