MySQL中如何基于另一列平均值关联对应文本列？

问题根源 #

你之前的错误在于试图在构建动态SQL的阶段就关联ref_values表，但此时avg_raw_xxx这类动态列还未生成，而且ROUND(CONCAT('avg_raw_',nsq), 0)的写法逻辑错误——CONCAT出来的是字符串（比如'avg_raw_overall_size'），不是实际的数值，自然无法匹配ref_values.key_name，导致报错"Unknown column REF.value"。

正确思路 #

要分两步走：

1. 先通过动态透视计算出每个widget的**原始平均值（avg_raw_xxx）**和标准差，得到基础结果集。
2. 基于这个结果集，针对每个avg_raw_xxx列，将其取整后关联ref_values表，匹配对应的文本答案，作为新列加入最终输出。

具体实现代码 #

第一步：生成动态透视SQL（计算原始平均值）

SET @phase_id = 1;
SET SESSION group_concat_max_len = 100000;
SET @PIVOT_SQL = NULL;
SET @TEXT_JOIN_SQL = NULL;

-- 生成动态透视的核心字段：avg_raw_xxx 和 std_dev_xxx
SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    'ROUND(IFNULL(AVG(CASE WHEN LOWER(REPLACE(RQ.short_question, '' '', ''_'')) = ''',
    nsq,
    ''' THEN IF(RA.answer = '''', 0, RA.answer) END), 0) + 0.0001, 2) AS `avg_raw_', nsq, '`,',
    'ROUND(IFNULL(STDDEV(CASE WHEN LOWER(REPLACE(RQ.short_question, '' '', ''_'')) = ''',
    nsq,
    ''' THEN RA.answer END), 0) + 0.0001, 3) AS `std_dev_', nsq, '`'
  )
  ORDER BY display_order
) INTO @PIVOT_FIELDS
FROM (
  SELECT 
    FD.ref_value, 
    RQ.display_order, 
    LOWER(REPLACE(RQ.short_question, ' ', '_')) as nsq
  FROM review_questions RQ
  LEFT JOIN form_data FD ON FD.id = RQ.form_data_id
  LEFT JOIN ref_values RV on FD.ref_value = RV.type 
  WHERE RQ.phase_id = @phase_id 
    AND FD.element_type = 'select' 
    AND RQ.is_active > 0
  GROUP BY FD.ref_value, RQ.display_order, nsq
  HAVING MAX(RV.key_name) REGEXP '^[0-9]+$'
) nq;

-- 拼接完整的透视查询SQL
SET @PIVOT_SQL = CONCAT(
  'SELECT widget_id, ', @PIVOT_FIELDS, ' ',
  'FROM review_answers RA ',
  'LEFT JOIN review_questions RQ ON RQ.id = RA.question_id ',
  'GROUP BY widget_id'
);

第二步：生成关联文本的动态SQL并执行

我们需要针对每个avg_raw_xxx列，动态生成关联ref_values的逻辑，获取对应的文本值：

-- 生成每个avg_raw_xxx对应的文本字段SQL
SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    '(SELECT rv.value FROM ref_values rv ',
    ' WHERE rv.type = ''', nq.ref_value, ''' ',
    ' AND rv.key_name = ROUND(p.`avg_raw_', nq.nsq, '`, 0)) AS `average_', nq.nsq, '`'
  )
  ORDER BY display_order
) INTO @TEXT_FIELDS
FROM (
  SELECT 
    FD.ref_value, 
    LOWER(REPLACE(RQ.short_question, ' ', '_')) as nsq,
    RQ.display_order
  FROM review_questions RQ
  LEFT JOIN form_data FD ON FD.id = RQ.form_data_id
  LEFT JOIN ref_values RV on FD.ref_value = RV.type 
  WHERE RQ.phase_id = @phase_id 
    AND FD.element_type = 'select' 
    AND RQ.is_active > 0
  GROUP BY FD.ref_value, nsq, RQ.display_order
  HAVING MAX(RV.key_name) REGEXP '^[0-9]+$'
) nq;

-- 拼接最终的完整SQL：透视结果 + 文本关联
SET @FINAL_SQL = CONCAT(
  'SELECT p.widget_id, ', @PIVOT_FIELDS, ', ', @TEXT_FIELDS, ' ',
  'FROM (', @PIVOT_SQL, ') p'
);

-- 执行最终SQL
PREPARE stmt FROM @FINAL_SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

效果说明 #

以你的overall_size例子为例：

• 透视子查询会计算出avg_raw_overall_size = 1.80
• 外层的子查询会执行ROUND(1.80,0)=2，然后关联ref_values表找到key_name=2对应的value='large'，最终输出average_overall_size='large'，和你期望的结果一致。

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