mysql - 如何匹配连接表和相关表中的列名和值？

Question

我有以下数据库结构:

汽车:

• ID
• 制作

特点:

• ID
• 姓名

汽车特征:

• ID
• car_id
• feature_id
• 值

我希望能够选择具有所有功能的所有汽车，并返回以 feature.name 作为列名称、cars_feature.value 作为值的结果。现在我能够获取所有功能和所有值，但我只知道如何使用group_concat来做到这一点。

我正在寻找的是以下输出:

car_id  car_make  color  wheels  doors
1       Ford      blue   alloy
2       Audi             alloy   3

数据示例:SQLFiddle

Answer 1

以下是我在 Combine multiple rows into one row MySQL 找到的代码的修改版本.

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(
      'MAX(CASE WHEN `name` = ''',
      `name`,
      ''' THEN value END) AS `',
      `name`, '`'
    )
  ) INTO @sql
FROM (SELECT name,
             value
      FROM features, car_feature
      WHERE features.id = car_feature.feature_id) AS car_features_feature;

SET @sql = CONCAT('SELECT car_feature.car_id AS car_id,
                          cars.make AS car_make,
                          ',
                          @sql,
                          '
                   FROM car_feature
                   LEFT JOIN cars ON car_feature.car_id = cars.id
                   LEFT JOIN features ON car_feature.feature_id = features.id
                   GROUP BY car_id;');

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

感谢您的学习经历。