mysql - 如何将一个表值显示为表行并显示另一表中的值

Question

我正在尝试组合并显示我的 2 个表格，如下所示，我的第一个表结构是

我的第二个表是

我的预期结果是

我尝试了这样的方法，但没有得到结果

select t2.field_value as t2.name
from content_fields t1
Join content_fields_type_richtext_data t2 ON t1.id = t2.field_id;

我的表结构有什么错误吗？如何得到这个结果。请帮助任何人

Answer 1

MySQL 没有 PIVOT 函数，但您可以使用带有 CASE 表达式的聚合函数将行数据转换为列:

select t2.object_id,
  max(case when t1.frontend_name = 'Bride Photo' then t2.field_value end) as `Bride Photo`,
  max(case when t1.frontend_name = 'BrideGroom Photo' then t2.field_value end) as `BrideGroom Photo`,
  max(case when t1.frontend_name = 'Bride Description' then t2.field_value end) as `Bride Description`,
  max(case when t1.frontend_name = 'Groom Description' then t2.field_value end) as `Groom Description`,
  max(case when t1.frontend_name = 'Wishes' then t2.field_value end) as `Wishes`
from content_fields t1
inner join content_fields_type_richtext_data t2 
  on t1.id = t2.field_id
group by t2.object_id;

如果您有未知或动态数量的值，那么您将需要使用准备好的语句来获取结果:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN t1.frontend_name = ''',
      frontend_name,
      ''' THEN t2.field_value END) AS `',
      frontend_name, '`'
    )
  ) INTO @sql
FROM content_fields;

SET @sql 
  = CONCAT('SELECT t2.object_id, ', @sql, ' 
            from content_fields t1
            inner join content_fields_type_richtext_data t2 
              on t1.id = t2.field_id
            group by t2.object_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;