MySQL - 將行轉換為列

Question

我嘗試搜尋帖子，但只找到了SQLServer/Access的解決方案。我需要MySQL(5.X)中的解決方案。我有一個包含3列的表（稱為歷史記錄）：hostid、itemname、itemvalue。如果我執行選擇（select*fromHistory），它將返回+--------+----------+-----------+|hostid|itemname |itemvalue|+-------

P粉920199761 · Answer

我將添加一個更長、更詳細的說明來說明解決此問題的步驟。如果太長，我深表歉意。

我將從您給出的基礎開始，並用它來定義幾個術語，我將在本文的其餘部分使用這些術語。這將是基底表：

select * from history;

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

這將是我們的目標，漂亮的資料透視表：

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

history.hostid 欄位中的值將成為資料透視表中的y 值。 history.itemname 欄位中的值將變為 x-values（出於顯而易見的原因）。

當我必須解決創建資料透視表的問題時，我使用三步驟過程來解決它（帶有可選的第四步）：

選擇感興趣的列，即 y 值和x 值
使用額外的列擴展基底表 - 每個 x 值一列
將擴充表分組和聚合 - 每個 y 值一組
（可選）美化聚合表

讓我們將這些步驟應用於您的問題，看看會得到什麼：

第 1 步：選擇感興趣的列。在所需的結果中，hostid 提供y 值，itemname 提供x 值。

第 2 步：使用額外列擴充基底表。我們通常需要每個 x 值一列。回想一下，我們的 x 值欄位是 itemname：

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
);

select * from history_extended;

+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A    | B    | C    |
+--------+----------+-----------+------+------+------+
|      1 | A        |        10 |   10 | NULL | NULL |
|      1 | B        |         3 | NULL |    3 | NULL |
|      2 | A        |         9 |    9 | NULL | NULL |
|      2 | C        |        40 | NULL | NULL |   40 |
+--------+----------+-----------+------+------+------+

請注意，我們沒有更改行數 - 我們只是添加了額外的列。另請注意 NULL 的模式 - itemname = "A" 的行的新列 A 具有非空值，並且其他新列的空值。

第 3 步：將擴充表進行分組和聚合。我們需要按 hostid 分組，因為它提供了 y 值：

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
);

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

（請注意，我們現在每個 y 值一行。） 好的，我們就快到了！我們只需要擺脫那些醜陋的 NULL 即可。

第四步：美化。我們將用零替換任何空值，以便結果集看起來更好：

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 建立了一個漂亮的資料透視表。

應用此程序時的注意事項：

在額外欄位中使用什麼值。我在此範例中使用了 itemvalue
在額外欄位中使用什麼「中性」值。我使用了 NULL，但也可能是 0 或 ""，這取決於您的特定情況
分組時使用什麼聚合函數。我使用了sum，但是count 和max 也經常使用（max 經常在建立單行時使用）分佈在多行中的“對象”）
使用多列表示 y 值。此解決方案不限於對 y 值使用單一列 - 只需將額外的列插入 group by 子句（並且不要忘記 select他們）

已知限制：

此解決方案不允許資料透視表中有 n 列 - 在擴充基底表時需要手動新增每個資料透視列。因此，對於 5 或 10 個 x 值，此解決方案很好。 100塊，不太好。有一些使用預存程序產生查詢的解決方案，但它們很醜陋且很難正確執行。當資料透視表需要有很多列時，我目前不知道有什麼好方法來解決這個問題。

MySQL - 將行轉換為列

全部回覆(1)我來回復