MySQL如何拼接同c_id的三行属性值为一列并解决1242报错？

• 子查询未关联外层的c_id过滤条件：每个子查询会匹配全表所有符合key条件的记录，自然返回多行触发1242错误
• 字段/值匹配错误：你代码里的表名consumer_attributes、字段名consumer_id、key值select_day/select_month/select_year和你提供的表结构不匹配，实际表名为attributes，关联字段为c_id，key值为day/month/year
• 冗余的嵌套CONCAT：不需要多层嵌套CONCAT，单个CONCAT函数就支持拼接多个参数

推荐使用分组+条件聚合的写法，兼容性强，性能也优于子查询写法：

SELECT
  c_id AS consumer_id,
  CONCAT(
    MAX(CASE WHEN `key` = 'day' THEN `value` END),
    '_',
    MAX(CASE WHEN `key` = 'month' THEN `value` END),
    '_',
    MAX(CASE WHEN `key` = 'year' THEN `value` END)
  ) AS `concat`
FROM attributes
GROUP BY c_id;

如果是MySQL 8.0+环境，也可以用行转列函数PIVOT实现，写法更简洁：

SELECT
  c_id AS consumer_id,
  CONCAT(day, '_', month, '_', year) AS `concat`
FROM attributes
PIVOT(
  MAX(`value`) FOR `key` IN ('day' AS day, 'month' AS month, 'year' AS year)
) AS pvt;

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