如何在MySQL中的列中旋轉行？

MySQL 數據表行列轉換詳解

引言

將數據表中以行表示觀測值、列表示屬性的數據轉換為觀測值變為列的表，是一種非常有用的數據操作技巧。本文將逐步講解如何在 MySQL 中實現這種轉換。

步驟 1：選擇目標列

確定將作為目標透視表中 y 值（結果表行）和 x 值（結果表列）的列。在本例中，“hostid” 將作為 y 值，“itemname” 將作為 x 值。

步驟 2：擴展基礎表添加額外列

為每個 x 值，向基礎表添加一個新列。使用 CASE 表達式根據 itemname 將這些列填充為相應的 itemvalue。

步驟 3：分組和聚合擴展表

按 y 值列對擴展表進行分組，並使用適當的函數（例如 SUM）對 x 值列進行聚合。

步驟 4：美化 (可選)

將聚合表中的任何 NULL 值替換為選定的中性值（例如 0），以創建視覺上更清晰的結果。

示例

考慮以下基礎表：

hostid	itemname	itemvalue
1	A	10
1	B	3
2	A	9
2	C	40

創建透視表

-- 步骤 1：选择目标列
SELECT hostid, itemname, itemvalue
FROM history;

-- 步骤 2：扩展基础表添加额外列
CREATE VIEW history_extended AS
SELECT
  history.*,
  CASE WHEN itemname = "A" THEN itemvalue END AS A,
  CASE WHEN itemname = "B" THEN itemvalue END AS B,
  CASE WHEN itemname = "C" THEN itemvalue END AS C
FROM history;

-- 步骤 3：分组和聚合扩展表
CREATE VIEW history_itemvalue_pivot AS
SELECT
  hostid,
  SUM(A) AS A,
  SUM(B) AS B,
  SUM(C) AS C
FROM history_extended
GROUP BY hostid;

-- 步骤 4：美化
CREATE VIEW history_itemvalue_pivot_pretty AS
SELECT 
  hostid, 
  COALESCE(A, 0) AS A, 
  COALESCE(B, 0) AS B, 
  COALESCE(C, 0) AS C 
FROM history_itemvalue_pivot;

-- 最终结果
SELECT *
FROM history_itemvalue_pivot_pretty;

結果

hostid	A	B	C
1	10	3	0
2	9	0	40

此示例演示瞭如何在 MySQL 中有效地重塑表以將行轉換為列。

以上是如何在MySQL中的列中旋轉行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！