如何使用 UNION ALL 和聚合在 MySQL 中模擬資料透視表？

模擬MySQL中的透視表：使用UNION ALL和聚合函數

資料分析與報表產生中，透視表扮演著關鍵角色，它將列式資料轉換為行式數據，從而實現更緊湊、更具洞察力的視覺化效果。雖然MySQL沒有直接提供UNPIVOT和PIVOT函數，但我們可以巧妙地結合UNION ALL和聚合函數來實現類似的功能。

資料展開（Unpivoting）

透視表的首要步驟是將資料展開，或將其轉換為更靈活的格式。這涉及為每一列資料建立多行，其中每一行代表一列及其對應值。我們可以使用UNION ALL操作符來實現：

<code class="language-sql">SELECT id, month, col1 AS value, 'col1' AS descrip
FROM yourTable
UNION ALL
SELECT id, month, col2 AS value, 'col2' AS descrip
FROM yourTable
UNION ALL
SELECT id, month, col3 AS value, 'col3' AS descrip
FROM yourTable
UNION ALL
SELECT id, month, col4 AS value, 'col4' AS descrip
FROM yourTable;</code>

此查詢將產生以下結果：

id	month	value	descrip
101	Jan	A	col1
102	Feb	C	col1
101	Jan	B	col2
102	Feb	A	col2
101	Jan	NULL	col3
102	Feb	G	col3
101	Jan	B	col4
102	Feb	E	col4

展開資料的透視（Pivoting）

資料展開後，我們可以使用聚合函數和CASE語句將其透視，以建立所需的報表格式：

<code class="language-sql">SELECT descrip,
  MAX(CASE WHEN month = 'Jan' THEN value ELSE 0 END) AS jan,
  MAX(CASE WHEN month = 'Feb' THEN value ELSE 0 END) AS feb
FROM (
    SELECT id, month, col1 AS value, 'col1' AS descrip
    FROM yourTable
    UNION ALL
    SELECT id, month, col2 AS value, 'col2' AS descrip
    FROM yourTable
    UNION ALL
    SELECT id, month, col3 AS value, 'col3' AS descrip
    FROM yourTable
    UNION ALL
    SELECT id, month, col4 AS value, 'col4' AS descrip
    FROM yourTable
) AS src
GROUP BY descrip;</code>

上述查詢將產生以下結果：

descrip	jan	feb
col1	A	C
col2	B	A
col3	0	G
col4	B	E

此結果與所需報表的格式相符。透過這些技術，我們可以有效地在MySQL中實現透視表功能，輕鬆地將基於列的資料轉換為基於行的報表，從而更有效率地進行分析和報告。

以上是如何使用 UNION ALL 和聚合在 MySQL 中模擬資料透視表？的詳細內容。更多資訊請關注PHP中文網其他相關文章！