MySQL-Pivot-Tabellenabfrage: Verwendung dynamischer Spalten für die Datenanalyse

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 beiden 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 funktioniert gut :)

Da ich die „zusätzliche“ Tabelle dynamisch fülle, wäre es schön, wenn die Abfrage auch dynamisch wäre. Auf diese Weise muss ich die Abfrage nicht jedes Mal ändern, wenn ich einen neuen Feldnamen und Feldwert eingebe.

Answer 1

我有一种稍微不同的方法来完成这个任务，与接受的答案不同。这种方法可以避免使用默认情况下有1024个字符限制的GROUP_CONCAT，并且如果你有很多字段，它将无法工作，除非你改变限制。

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