測試環境為 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 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') , ('Ben' , 'Note10+' , '1') , ('Mark' , 'Zenfone6' , '2') , ('Mark' , 'Apple11' , '2') , ('Ben' , 'Note10+' , '1') , ('Mark' , 'Apple11' , '1');
Query OK, 6 rows affected (0.002 sec)
Records: 6 Duplicates: 0 Warnings: 0
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 | +-------+---------+---------+----------+
可以透過如下的語法:
語法1:
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;
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 使用,請參考 – https://benjr.tw/101629
語法2: Prepared Statement
先來看一下什麼是 Prepared Statement .
MariaDB [testdb]> PREPARE stmt FROM 'SELECT staff Staff , saleitem Item ,SUM(salenum) SUM FROM sale WHERE Staff=? GROUP BY saleitem , staff ORDER BY Staff , saleitem'; Query OK, 0 rows affected (0.001 sec) Statement prepared MariaDB [testdb]> EXECUTE stmt USING 'Mark'; +-------+----------+------+ | Staff | Item | SUM | +-------+----------+------+ | Mark | Apple11 | 3 | | Mark | Zenfone6 | 2 | +-------+----------+------+ 2 rows in set (0.001 sec) MariaDB [testdb]> SET @param='Ben'; Query OK, 0 rows affected (0.000 sec) MariaDB [testdb]> EXECUTE stmt USING @param; +-------+---------+------+ | Staff | Item | SUM | +-------+---------+------+ | Ben | Apple11 | 1 | | Ben | Note10+ | 2 | +-------+---------+------+ 2 rows in set (0.001 sec)
語法說明:
- PREPARE stmt FROM
後面就是接著接下來要執行的 SQL 語法,可以使用 ? (可使用多個) 來當 execute 的參數. - execute stmt USING @param;
執行前面由 PREPARE 的 SQL 語法,後面 USING 就是要輸入 PREPARE 的參數 (多個參數時使用 , 來區隔).
語法1缺點是需要預先知道有哪一些 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;
語法說明:
- 設定一個儲存資料變數 sql .
SET @sql = NULL;
- 這一段主要是產生 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 …]
- CONCAT
- 產生完整 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 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ - 透過 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)
語法2: Prepared Statement + 存儲資料到暫時表 (TEMPORARY TABLE)
需要透過 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');
SET @createSQL = CONCAT('CREATE TEMPORARY TABLE tmp AS ', @sql);
PREPARE stmt FROM @createSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT * FROM tmp;
DROP TEMPORARY TABLE tmp;
語法說明:
新增下面這兩段程式
-
SET @createSQL = CONCAT('CREATE TEMPORARY TABLE tmp AS ', @sql);主要功能是產生一個暫存表並把後面 SELECT 到的資料儲存到 tmp 這一個暫存表.
可以看一下 @createSQL 的內容.
MariaDB [testdb]> SELECT @createSQL; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | @createSQL | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE TEMPORARY TABLE tmp AS 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 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) -
DROP TEMPORARY TABLE tmp;
暫存表不用時要移除掉.
沒有解決問題,試試搜尋本站其他內容