MySQL- Convert Column to Row (Pivot Table 樞紐分析表)

測試環境為 CentOS 7 x86_64 (虛擬機)

將資料庫的列 (Column) 轉換為行 (row) 來呈現 (Pivot Table : 樞紐分析表) 其統計結果,如下圖所示.

先建立要測試用的資料庫.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE testdb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'testdb' at line 1
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE sale (K1 INT(11) NOT NULL AUTO_INCREMENT , staff VARCHAR(199) NOT NULL , saleitem VARCHAR(199) NOT NULL, salenum INT(11) NOT NULL, PRIMARY KEY (K1));
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> INSERT INTO sale(staff , saleitem , salenum) VALUES('Ben' , 'Apple11' , '1');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO sale(staff , saleitem , salenum) VALUES('Ben' , 'Note10+' , '1');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO sale(staff , saleitem , salenum) VALUES('Mark' , 'Zenfone6' , '2');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO sale(staff , saleitem , salenum) VALUES('Mark' , 'Apple11' , '2');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO sale(staff , saleitem , salenum) VALUES('Ben' , 'Note10+' , '1');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO sale(staff , saleitem , salenum) VALUES('Mark' , 'Apple11' , '1');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> SELECT * FROM sale;
+----+-------+----------+---------+
| K1 | staff | saleitem | salenum |
+----+-------+----------+---------+
|  1 | Ben   | Apple11  |       1 |
|  2 | Ben   | Note10+  |       1 |
|  3 | Mark  | Zenfone6 |       2 |
|  4 | Mark  | Apple11  |       2 |
|  5 | Ben   | Note10+  |       1 |
|  6 | Mark  | Apple11  |       1 |
+----+-------+----------+---------+
6 rows in set (0.00 sec)

利用 GROUP BY 以及 ORDER BY 來統計資料.

MariaDB [testdb]> SELECT staff Staff , saleitem Item ,SUM(salenum) SUM FROM sale GROUP BY saleitem , staff ORDER BY staff , saleitem;
+-------+----------+------+
| Staff | Item     | SUM  |
+-------+----------+------+
| Ben   | Apple11  |    1 |
| Ben   | Note10+  |    2 |
| Mark  | Apple11  |    3 |
| Mark  | Zenfone6 |    2 |
+-------+----------+------+
4 rows in set (0.00 sec)

使用如下函數:

  • SUM
    數字加總.

要怎做才能依據使用者及銷售物品名稱來統計呢? 如下表所示.

+-------+---------+---------+----------+
| staff | Apple11 | Note10+ | Zenfone6 |
+-------+---------+---------+----------+
| Ben   |       1 |       2 |        0 |
| Mark  |       3 |       0 |        2 |
+-------+---------+---------+----------+

可以透過如下的語法:

MariaDB [testdb]> SELECT
    ->   staff ,
    ->   SUM(CASE WHEN saleitem = 'Apple11' THEN salenum ELSE 0 END) 'Apple11' ,
    ->   SUM(CASE WHEN saleitem = 'Note10+' THEN salenum ELSE 0 END) 'Note10+' ,
    ->   SUM(CASE WHEN saleitem = 'Zenfone6' THEN salenum ELSE 0 END) 'Zenfone6'
    -> FROM sale GROUP BY staff;
+-------+---------+---------+----------+
| staff | Apple11 | Note10+ | Zenfone6 |
+-------+---------+---------+----------+
| Ben   |       1 |       2 |        0 |
| Mark  |       3 |       0 |        2 |
+-------+---------+---------+----------+
2 rows in set (0.00 sec)

關於 CASE 使用,請參考 – http://benjr.tw/101629

上面語法缺點是需要預先知道有哪一些 saleitem 項目,並依據項目產生 SUM 的敘述,有沒有辦法讓 SQL 語法自己產生關於 SUM 的這一段敘述呢!

需要透過 Prepared Statement 的方式來做.

SET @sql = NULL;
SELECT     
    GROUP_CONCAT(DISTINCT     
    CONCAT(       
       'SUM(CASE WHEN saleitem = "', saleitem , '" THEN salenum ELSE 0 end) AS ' , '"' , saleitem , '"')   
    ) 
INTO @sql
FROM sale;

SET @sql = CONCAT('SELECT staff , ', @sql, ' 
                  FROM sale
                   GROUP BY staff');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

語法說明:

  1. 設定一個儲存資料變數 sql .
    SET @sql = NULL; 
    
  2. 這一段主要是產生 SUM 這一段語法,並儲存到變數 sql.
    SELECT     
        GROUP_CONCAT(DISTINCT     
        CONCAT(       
           'SUM(CASE WHEN saleitem = "', saleitem , '" THEN salenum ELSE 0 end) AS ' , '"' , saleitem , '"')   
        ) 
    INTO @sql
    FROM sale;
    

    上一段語法執行結果會儲存到 sql 變數.

    MariaDB [testdb]> SELECT @sql;
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | @sql                                                                                                                                                                                                                         |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | SUM(CASE WHEN saleitem = "Apple11" THEN salenum ELSE 0 end) AS "Apple11",SUM(CASE WHEN saleitem = "Note10+" THEN salenum ELSE 0 end) AS "Note10+",SUM(CASE WHEN saleitem = "Zenfone6" THEN salenum ELSE 0 end) AS "Zenfone6" |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    使用如下函數:

    • CONCAT
      可以將兩個或更多個字串連接在一起.
      CONCAT(expression1, expression2, expression3,…)
    • GROUP_CONCAT
      透過 GROUP_CONCAT 的 DISTINCT 功能,只保留不重覆的字串.
      GROUP_CONCAT([DISTINCT] expr [,expr …]
  3. 產生完整 SELECT 語法.
    SET @sql = CONCAT('SELECT staff , ', @sql, ' 
                      FROM sale
                       GROUP BY staff');
    

    上一段語法執行結果會儲存到 sql 變數.

    MariaDB [testdb]> SELECT @sql;
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | @sql                                                                                                                                                                                                                                                                                                       |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | SELECT staff , SUM(CASE WHEN saleitem = "Apple11" THEN salenum ELSE 0 end) AS "Apple11",SUM(CASE WHEN saleitem = "Note10+" THEN salenum ELSE 0 end) AS "Note10+",SUM(CASE WHEN saleitem = "Zenfone6" THEN salenum ELSE 0 end) AS "Zenfone6" 
                      FROM sale
                       GROUP BY staff |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    
  4. 透過 Prepared Statement 的方式來執行剛剛產生的語法.
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    

    透過 DEALLOCATE 或 DROP 來刪除指定的 prepared statement .

    DEALLOCATE PREPARE stmt;
    

執行結果:

MariaDB [testdb]> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> SELECT     
    ->     GROUP_CONCAT(DISTINCT     
    ->     CONCAT(       
    ->        'SUM(CASE WHEN saleitem = "', saleitem , '" THEN salenum ELSE 0 end) AS ' , '"' , saleitem , '"')   
    ->     ) 
    -> INTO @sql
    -> FROM sale;
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> 
MariaDB [testdb]> SET @sql = CONCAT('SELECT staff , ', @sql, ' 
    '>                   FROM sale
    '>                    GROUP BY staff');
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> 
MariaDB [testdb]> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [testdb]> EXECUTE stmt;
+-------+---------+---------+----------+
| staff | Apple11 | Note10+ | Zenfone6 |
+-------+---------+---------+----------+
| Ben   |       1 |       2 |        0 |
| Mark  |       3 |       0 |        2 |
+-------+---------+---------+----------+
2 rows in set (0.00 sec)

MariaDB [testdb]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料