MySQL 中的資料透視表
在本文中,我們將介紹如何將表資料從行轉換為列。這個過程稱為旋轉。這種轉換的結果通常是一個彙總表,我們在其中提供適合報告生成的資訊。
在 MySQL 中,沒有建立資料透視表的內建函式,因此你必須編寫一個 MySQL 查詢來生成資料透視表。幸運的是,有三種不同的方法可以使用 MySQL 建立資料透視表。
- 在 MySQL 中使用
IF
語句建立資料透視表 - 使用
CASE
語句在 MySQL 中建立資料透視表 - 使用動態資料透視列在 MySQL 中建立資料透視表
以下指令碼建立一個包含三列(name
、subjectid
和 marks
)的表 student。
create table student(name varchar(20),subjectid int(10), marks int(10));
在這裡,我們將樣本資料值插入
到學生表中以進行演示。
insert into student values ('Sam',1,70);
insert into student values ('Sam',2,77);
insert into student values ('Sam',3,71);
insert into student values ('Sam',4,70);
insert into student values ('Sam',1,99);
insert into student values ('John',1,89);
insert into student values ('John',2,87);
insert into student values ('John',3,88);
insert into student values ('John',4,89);
insert into student values ('Martin',1,60);
insert into student values ('Martin',2,47);
insert into student values ('Martin',3,68);
insert into student values ('Martin',4,39);
插入這些值後,讓我們編寫一個 select
查詢來檢視資料庫中的記錄。
Select * from student;
輸出將是:
在 MySQL 中使用 MAX
函式和 IF
語句建立資料透視表
這是解決問題的非常直接的方法。在這種方法中,我們將使用 MAX
函式和 IF
語句。如果資料庫中某個學生的單個科目有多個條目,則最多需要兩分。
現在讓我們看看如何在 SELECT
查詢中使用它:
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;
輸出將是:
該輸出是針對每個學生的學科的編譯結果。由於你希望每個學生佔一行,因此你需要按姓名列進行分組。此外,你需要為每一列指定一個條件,即每個主題一個條件。
在 MySQL 中使用 CASE
語句建立資料透視表
這也是解決問題的直接方法。在這種方法中,我們將使用 MAX
函式和 CASE
語句。此外,如果資料庫中的某個學生存在多個針對單個學科的條目,則最多需要兩分。同樣,如果某些學生的分數不存在,則取 NULL 值。
現在讓我們看看如何在 SELECT
查詢中使用它:
SELECT name,
MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) AS Sub1,
MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) AS Sub2,
MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) AS Sub3,
MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) AS Sub4
FROM student
GROUP BY name;
輸出將是:
在 MySQL 中使用動態資料透視列建立資料透視表
聚合函式 (MAX
)、IF
語句和 CASE
語句生成了上述示例中的資料透視表。使用這種方法的缺點是我們在編寫查詢時需要知道列標題,當列數增加時,程式碼也會增加。我們可以採用上述兩種方法來獲得較小的結果和所有可能的值。
因此,為了克服這些限制,我們可以使用動態樞軸列。在這裡,GROUP_CONCAT
函式可以動態生成 PIVOT
表輸出的列。
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
' MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END)
AS "', subjectid, '"')
)
INTO @sql FROM student;
SET @sql = CONCAT('SELECT name, ', @sql,
' FROM student GROUP BY name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
在 GROUP_CONCAT
函式中,我們使用 DISTINCT
關鍵字來獲取唯一的標記列表。否則,我們的查詢會因為列表太長而出錯。你可以隨時編寫 Select * from @sql
來檢查動態查詢。
現在,當我們執行這個查詢時,這就是我們得到的結果:
請注意,列標題是根據表中的值動態生成的,並且列標題表示 subjectid
。