MySQL 中的資料透視表

Sweety Rupani 2023年1月30日 2022年5月14日
  1. 在 MySQL 中使用 MAX 函式和 IF 語句建立資料透視表
  2. 在 MySQL 中使用 CASE 語句建立資料透視表
  3. 在 MySQL 中使用動態資料透視列建立資料透視表
MySQL 中的資料透視表

在本文中,我們將介紹如何將表資料從行轉換為列。這個過程稱為旋轉。這種轉換的結果通常是一個彙總表,我們在其中提供適合報告生成的資訊。

在 MySQL 中,沒有建立資料透視表的內建函式,因此你必須編寫一個 MySQL 查詢來生成資料透視表。幸運的是,有三種不同的方法可以使用 MySQL 建立資料透視表。

  1. 在 MySQL 中使用 IF 語句建立資料透視表
  2. 使用 CASE 語句在 MySQL 中建立資料透視表
  3. 使用動態資料透視列在 MySQL 中建立資料透視表

以下指令碼建立一個包含三列(namesubjectidmarks)的表 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 中的資料透視表 - 示例

在 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 中的資料透視表-if 語句

該輸出是針對每個學生的學科的編譯結果。由於你希望每個學生佔一行,因此你需要按姓名列進行分組。此外,你需要為每一列指定一個條件,即每個主題一個條件。

在 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 中的資料透視表-case 語句

在 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 來檢查動態查詢。

現在,當我們執行這個查詢時,這就是我們得到的結果:

mysql 中的資料透視表 - 動態資料透視列

請注意,列標題是根據表中的值動態生成的,並且列標題表示 subjectid

相關文章 - MySQL Table