MySQL透視表查詢：利用動態列實現資料分析

Question

我正在使用以下表格來儲存產品資料：

mysql> SELECT * FROM product;
 --------------- --------------- --------
| id | name | description | stock |
 --------------- --------------- --------
| 1 | product1 | first product | 5 |
| 2 | product2 | second product| 5 |
 --------------- --------------- --------

mysql> SELECT * FROM product_additional;
 ----------------- ------------
| id | fieldname | fieldvalue |
 ----------------- ------------
| 1 | size | S |
| 1 | height | 103 |
| 2 | size | L |
| 2 | height | 13 |
| 2 | color | black |
 ----------------- ------------ 

使用以下查詢從兩個表中選擇記錄：

mysql> SELECT
    p.id
    , p.name
    , p.description
    ,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) as `size`
    ,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) as `height`
    ,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) as `color`
FROM product p
LEFT JOIN product_additional AS pa ON p.id = pa.id
GROUP BY p.id
 --------------- --------------- -------- --------- --- -----
| id | name | description | size | height | color |
 --------------- --------------- -------- --------- --- -----
| 1 | product1 | first product | S | 103 | null |
| 2 | product2 | second product| L | 13 | black |
 --------------- --------------- -------- --------- --- ----- 

一切都正常運作 :)

因為我動態填入「additional」表，所以如果查詢也是動態的就好了。這樣我就不必每次輸入新的欄位名稱和欄位值時都要更改查詢。

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