HOWTO · MySQL

MySQL 中的資料透視表

本文演示了在 MySQL 中生成資料透視表的不同方法。

本頁內容

資料透視會將資料列轉換成欄,常用於製作摘要或報表。MySQL 沒有內建的 PIVOT 函式,但可使用條件式彙總搭配 IF 或 CASE 建立固定欄位。當輸出欄位集合會變動時,動態 SQL 可以根據資料組成查詢。本文以學生分數為例:按學生分組會讓每個姓名各占一列,而每個科目則成為一欄。

準備範例資料

範例中,Sam 在科目 1 有兩筆分數。預期的透視結果會採用較高的分數 99。Martin 沒有科目 4 的資料列,因此固定欄位範例在該欄會顯示 NULL。提供的輸入資料圖呈現了下方範例使用的相同記錄。

學生輸入資料表,包含 name、subjectid 和 marks 欄

輸入記錄中,Sam 的科目 1 有兩筆分數,因此透視結果應顯示較高的數值 99。

執行此範例:

本節中的程式碼以及顯示的標籤或輸出仍為英文。

rows = [
    ("Sam", 1, 70), ("Sam", 2, 77), ("Sam", 3, 71), ("Sam", 4, 70),
    ("Sam", 1, 99), ("John", 1, 89), ("John", 2, 87),
    ("John", 3, 88), ("John", 4, 89), ("Martin", 1, 60),
    ("Martin", 2, 47), ("Martin", 3, 68),
]
subjects = (1, 2, 3, 4)
print("name|" + "|".join(f"Sub{s}" for s in subjects))
for name in sorted({row[0] for row in rows}):
    marks = [max((score for student, subject, score in rows
                  if student == name and subject == s), default="NULL")
             for s in subjects]
    print(name + "|" + "|".join(str(score) for score in marks))
name|Sub1|Sub2|Sub3|Sub4
John|89|87|88|89
Martin|60|47|68|NULL
Sam|99|77|71|70

這段 Python 示意程式呈現了分組與取最高分的行為;請將下方的 MySQL 查詢用於你的資料表。SQL 建表語句為 CREATE TABLE student (name VARCHAR(20), subjectid INT, marks INT);。加入圖中所示的資料列,包括 Sam 的兩筆科目 1 記錄,再使用條件式彙總建立報表。

使用 IF 和 MAX 建立透視表

每個科目欄都撰寫一個條件式,接著按 name 分組,讓每位學生各占一列:SELECT name, MAX(IF(subjectid=1, marks, NULL)) AS Sub1, MAX(IF(subjectid=2, marks, NULL)) AS Sub2, MAX(IF(subjectid=3, marks, NULL)) AS Sub3, MAX(IF(subjectid=4, marks, NULL)) AS Sub4 FROM student GROUP BY name;。IF 遇到符合條件的資料列時會傳回分數,否則傳回 NULL。MAX 會在每個姓名和科目欄中彙總這些值。如果同一位學生的同一科目有多筆符合條件的資料列,MAX 會傳回其中最高的分數;它不會只選第一筆,也不會限制資料列數量。

條件式彙總的固定欄位透視結果

固定欄位結果顯示 Sam 的科目 1 最高分 99;Martin 沒有科目 4 的記錄,因此該欄為 NULL。

圖中顯示此查詢的結果,包括 Sam 的科目 1 分數 99,以及 Martin 缺少科目 4 記錄時的 NULL。這些是結果示意圖,不是資料庫用戶端的螢幕截圖。SQL 和結果在本文中仍可選取複製。

使用 CASE 建立透視表

CASE 可明確表達相同的條件。此查詢會產生與 IF 版本相同的固定欄位結果:SELECT name, MAX(CASE WHEN subjectid=1 THEN marks END) AS Sub1, MAX(CASE WHEN subjectid=2 THEN marks END) AS Sub2, MAX(CASE WHEN subjectid=3 THEN marks END) AS Sub3, MAX(CASE WHEN subjectid=4 THEN marks END) AS Sub4 FROM student GROUP BY name;。未指定 ELSE 時,不符合條件的資料列會得到 NULL;MAX 會忽略這些不符合條件的值。因此,若某位學生沒有該科目的資料列,結果仍會是 NULL。重複符合條件的資料列也依相同的取最大值規則處理。

產生動態透視欄

IF 和 CASE 查詢的輸出欄位是固定的:撰寫查詢時必須先知道科目值,並為每個欄位加入一個運算式。動態 SQL 可以根據不同的科目 ID 產生這些運算式。GROUP_CONCAT 會組合欄位運算式,再由預備陳述式執行產生的查詢:

SET SESSION group_concat_max_len = 100000; SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END) AS \\’, subjectid, ‘\') ORDER BY subjectid SEPARATOR ', ') INTO @columns FROM student; SET @sql = CONCAT('SELECT name, ', @columns, ' FROM student GROUP BY name'); SELECT @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

產生的查詢會為資料表中每個科目建立一個輸出欄位。在這個範例中,分數都不是負數,因此 ELSE 0 會讓缺少的科目顯示為 0,與動態結果圖相符。只有在分數不可能為負數時,這種寫法才正確:否則,不符合條件的資料列所產生的 0,可能會在 MAX 中大於符合條件的負分數。如果分數可能為負數,請改用 ELSE NULL;如此一來,MAX 會忽略不符合條件的資料列,而學生沒有該科目資料時,該欄會得到 NULL。在預備陳述式執行前,請先檢查 @sql,尤其是值或識別字可能來自不可信輸入時。產生的陳述式與預備陳述式都以工作階段為範圍,因此這些陳述式必須在同一個 MySQL 連線中執行。GROUP_CONCAT 的最大長度可設定;若產生的運算式清單超過 group_concat_max_len,內容可能會被截斷。請設定合適的工作階段值,並在執行前檢查產生的 SQL。

以科目為欄的動態透視結果

產生的結果會為每個科目 ID 新增一欄;在此範例分數皆非負數的假設下,Martin 缺少科目 4 的分數會顯示為 0。

此方法會依資料中的科目 ID 調整欄位清單。不過,陳述式預備完成時,結果的欄位結構就已確定;如果科目數量非常多或經常變動,改用應用程式程式碼或報表工具可能更合適。