Dynamische Spalten-MySQL-PivotTable-Abfrage

Question

Ich verwende die folgende Tabelle zum Speichern von Produktdaten:

mysql>
+---------------+---------------+--------+
|. id |. Beschreibung |
+---------------+---------------+--------+
|. 1 |. erstes Produkt |
|. 2 |. zweites Produkt|
+---------------+---------------+--------+

mysql> SELECT * FROM product_additional;
+-----------------+----------------+
|. id |. Feldname |
+-----------------+----------------+
|. 1 |. Größe |
|. 1 |. Höhe |
|. 2 |. Größe |
|. 2 |. Höhe |
|. 2 |. Farbe |
+-----------------+----------------+

Verwenden Sie die folgende Abfrage, um Datensätze aus zwei Tabellen auszuwählen

mysql>
    p.id
    ,p.name
    , S. Beschreibung
    ,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) als `size`
    ,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) als `height`
    ,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) als `color`
AB Produkt S
LEFT JOIN product_additional AS pa ON p.id = pa.id
GRUPPE NACH p.id
+---------------+---------------+--------+-------- -+--------+
|. id |. Beschreibung |
+---------------+---------------+--------+-------- -+--------+
|. 1 |. erstes Produkt |
|. 2 |. zweites Produkt|
+---------------+---------------+--------+-------- -+--------+

Alles ist gut :)

Da ich die „Append“-Tabelle dynamisch fülle, wäre es toll, wenn die Abfrage ebenfalls dynamisch wäre. Auf diese Weise muss ich die Abfrage nicht jedes Mal ändern, wenn ich einen neuen Feldnamen und Feldwert eingebe.

Answer 1

我的做法与接受的答案略有不同。这样您就可以避免使用 GROUP_CONCAT，它默认限制为 1024 个字符，如果您有很多字段，则该限制将不起作用，除非您更改限制。

SET @sql = '';
SELECT
    @sql := CONCAT(@sql,if(@sql='','',', '),temp.output)
FROM
(
    SELECT
      DISTINCT
        CONCAT(
         'MAX(IF(pa.fieldname = ''',
          fieldname,
          ''', pa.fieldvalue, NULL)) AS ',
          fieldname
        ) as output
    FROM
        product_additional
) as temp;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id, p.name, p.description');

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

点击此处查看演示

Answer 2

MySQL 中动态执行此操作的唯一方法是使用准备好的语句。这是一篇关于它们的好文章：

动态数据透视表（将行转换为列）

您的代码将如下所示：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.fieldname = ''',
      fieldname,
      ''', pa.fieldvalue, NULL)) AS ',
      fieldname
    )
  ) INTO @sql
FROM product_additional;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id, p.name, p.description');

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

参见演示

注意：GROUP_CONCAT 函数的长度限制为 1024 个字符。查看参数group_concat_max_len