SQL 語法 – CTE (Common Table Expression)

SELECT 後的資料有辦法儲存到變數嗎,有的請參考 – https://benjr.tw/102432 , 那如果是要儲存整張 SELECT 後的 Table 呢! 可以使用 CTE (Common Table Expression).想要存取階層式的資料可以透過 Recursive Common Table Expressions ,使用方式請參考 – https://benjr.tw/103485

測試環境為 CentOS 8 x86_64 , Mariadb 10.3.17-1 (虛擬機)

先建立一個測試用資料庫 (testdb) , 與 tables (employee) 格式為 K1 int(11) – auto_increment & PRIMARY KEY , Name char(20), Dept char(20), jobTitle char(20) ,email char(30) 以及 Salary int(11).

SELECT 查詢需注意資料庫字元編碼 Character Sets 與文字排序 Collations ,請參考資料庫編碼 – https://benjr.tw/102156 .

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.11-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 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (K1 int(11) NOT NULL auto_increment, Name char(20) NOT NULL, Dept char(20) NOT NULL, JobTitle char(20) NOT NULL, Salary int(11) NOT NULL , email char(30) NOT NULL, PRIMARY KEY (K1)) ;
Query OK, 0 rows affected (0.010 sec)
 
MariaDB [testdb]> DESCRIBE employee; 
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| K1       | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name     | char(20) | NO   |     | NULL    |                |
| Dept     | char(20) | NO   |     | NULL    |                |
| JobTitle | char(20) | NO   |     | NULL    |                |
| Salary   | int(11)  | NO   |     | NULL    |                |
| email    | char(30) | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
6 rows in set (0.004 sec)

透過 INSERT 新增加了五筆資料.

MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary , email) VALUES ('Ben' , 'Testing' , 'Engineer' , '45000' , 'ben@benjr.tw') , ('Afa' , 'Power' , 'Engineer' , '48000' , 'Afa@benjr.tw') , ('Boss' ,'Testing' , 'Manager' , '75000' , 'Boss@benjr.tw') , ('JoJo' , 'Testing' , 'Engineer' , '45000' , 'jojo@benjr.tw') ,  ('Ian' , 'PM' , 'Engineer' , '85000' , 'ian@benjr.tw');
Query OK, 5 rows affected (0.002 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
MariaDB [testdb]> SELECT * FROM employee;
+----+------+---------+----------+--------+---------------+
| K1 | Name | Dept    | JobTitle | Salary | email         |
+----+------+---------+----------+--------+---------------+
|  1 | Ben  | Testing | Engineer |  45000 | ben@benjr.tw  |
|  2 | Afa  | Power   | Engineer |  48000 | Afa@benjr.tw  |
|  3 | Boss | Testing | Manager  |  75000 | Boss@benjr.tw |
|  4 | JoJo | Testing | Engineer |  45000 | jojo@benjr.tw |
|  5 | Ian  | PM      | Engineer |  85000 | ian@benjr.tw  |
+----+------+---------+----------+--------+---------------+
5 rows in set (0.001 sec)

先來看一下範例,利用 GROUP BY Dept 可以統計各單位薪水的總數.

MariaDB [testdb]> SELECT IFNULL(Dept,"Total") AS DEPT , SUM(Salary) AS "DEPT Salary" FROM employee GROUP BY Dept WITH ROLLUP;
+---------+-------------+
| DEPT    | DEPT Salary |
+---------+-------------+
| PM      |       85000 |
| Power   |       48000 |
| Testing |      165000 |
| Total   |      298000 |
+---------+-------------+

有辦法算順計算一下 % 百分比嗎?

+---------+-------------+------------+
| DEPT    | DEPT Salary | Percentage |
+---------+-------------+------------+
| PM      |       85000 | 28.52%     |
| Power   |       48000 | 16.10%     |
| Testing |      165000 | 55.36%     |
| Total   |      298000 | 100.00%    |
+---------+-------------+------------+

Sub-Query

利用 Sub-Query 先計算出總數然後再與各單位相除.

MariaDB [testdb]> SELECT IFNULL(Dept,"Total") AS DEPT ,SUM(Salary) AS "DEPT Salary", CONCAT(TRUNCATE(SUM(Salary)*100/(SELECT SUM(Salary) FROM employee),2),"%") AS "Percentage" FROM employee GROUP BY Dept WITH ROLLUP;
+---------+-------------+------------+
| DEPT    | DEPT Salary | Percentage |
+---------+-------------+------------+
| PM      |       85000 | 28.52%     |
| Power   |       48000 | 16.10%     |
| Testing |      165000 | 55.36%     |
| Total   |      298000 | 100.00%    |
+---------+-------------+------------+
4 rows in set, 1 warning (0.001 sec)

函數說明:

IFNULL : 用來判斷 Expression 字串(第一個參數)是否為 NULL ,是就以 ALTER_Value (第二個參數)來取代.
SUM : 數字加總. 通常搭配 GROUP BY 使用.
CONCAT : 可以將兩個或更多個字串連接在一起.
TRUNCATE : 取小數點,不計算四捨五入.

SET

上面方式雖然好但是會比教需要計算時間,有辦法總數先儲存起來嗎? 可以使用 set 儲存到一個變數,用法請參考 https://benjr.tw/102432 .

MariaDB [testdb]> SET @SalaryTemp =(SELECT SUM(Salary) FROM employee);
Query OK, 0 rows affected (0.001 sec)

MariaDB [testdb]> SELECT @SalaryTemp;
+-------------+
| @SalaryTemp |
+-------------+
|      298000 |
+-------------+
1 row in set (0.000 sec)

MariaDB [testdb]> SELECT IFNULL(Dept,"Total") AS DEPT ,SUM(Salary) AS "DEPT Salary", CONCAT(TRUNCATE(SUM(Salary)*100/@SalaryTemp,2),"%") AS "Percentage" FROM employee GROUP BY Dept WITH ROLLUP;
+---------+-------------+------------+
| DEPT    | DEPT Salary | Percentage |
+---------+-------------+------------+
| PM      |       85000 | 28.52%     |
| Power   |       48000 | 16.10%     |
| Testing |      165000 | 55.36%     |
| Total   |      298000 | 100.00%    |
+---------+-------------+------------+
4 rows in set, 1 warning (0.001 sec)

CTE (Common Table Expression)

有辦法先算出個單位與全公司的薪水總數並暫存起來嗎?可以使用 CTE (Common Table Expression) 把 SELECT 後的 Table 暫存起來.

先來看一下我們要暫存的資料表,有使用到 OVER 與 PARTITION BY 用法 ,詳細請參考 – https://benjr.tw/102501

MariaDB [testdb]> SELECT Name , Dept , JobTitle , Salary, SUM(Salary) OVER (PARTITION BY Dept) dep_sum , SUM(Salary) OVER () total_sum FROM employee;
+------+---------+----------+--------+---------+-----------+
| Name | Dept    | JobTitle | Salary | dep_sum | total_sum |
+------+---------+----------+--------+---------+-----------+
| Ian  | PM      | Engineer |  85000 |   85000 |    298000 |
| Afa  | Power   | Engineer |  48000 |   48000 |    298000 |
| JoJo | Testing | Engineer |  45000 |  165000 |    298000 |
| Boss | Testing | Manager  |  75000 |  165000 |    298000 |
| Ben  | Testing | Engineer |  45000 |  165000 |    298000 |
+------+---------+----------+--------+---------+-----------+
5 rows in set (0.001 sec)

來看一下 OVER PARTITION BY 的使用方式.

SUM(Salary) OVER (PARTITION BY Dept)

PARTITION BY Dept – 會依據 Dept 欄位來分類.
SUM(Salary) OVER – 會依據 PARTITION BY Dept 分類的結果來加總 (SUM).
AS Dept_Total – 欄位就會顯示依據各單位 Dept 欄位的加總

如果 OVER 不指定欄位,就會把全部資料的 Salary 欄位加總.

使用 CTE 把剛剛 SELECT 的資料表暫存名稱為 Salary_temp ,也把其中各單位薪水總數欄位別名為 dep_sum ,及全公司薪水欄位別名為 total_sum.後續再從暫存資料表 Salary_temp 中 SELECT+ GROUP BY 需要的資料.

WITH Salary_temp AS 
(
 SELECT Name , Dept , JobTitle , Salary, 
        SUM(Salary) OVER (PARTITION BY Dept) dep_sum , 
        SUM(Salary) OVER () total_sum 
 FROM employee
)
SELECT Dept , dep_sum AS Dept_Total , total_sum AS Total , dep_sum*100/total_sum  AS Percentage FROM Salary_temp GROUP BY Dept;

MariaDB [testdb]> WITH Salary_temp AS (SELECT Name , Dept , JobTitle , Salary, SUM(Salary) OVER (PARTITION BY Dept) dep_sum , SUM(Salary) OVER () total_sum FROM employee)  SELECT Dept , dep_sum AS Dept_Total , total_sum AS Total , dep_sum*100/total_sum  AS Percentage FROM Salary_temp GROUP BY Dept;
+---------+------------+--------+------------+
| Dept    | Dept_Total | Total  | Percentage |
+---------+------------+--------+------------+
| PM      |      85000 | 298000 |    28.5235 |
| Power   |      48000 | 298000 |    16.1074 |
| Testing |     165000 | 298000 |    55.3691 |
+---------+------------+--------+------------+
3 rows in set (0.001 sec)

CTE 使用上有些限制 – https://dev.mysql.com/doc/refman/8.0/en/with.html
在使用 recursive CTE subqueries 時不能包含以下的結構.

Aggregate functions such as SUM()
Window functions
GROUP BY
ORDER BY
DISTINCT

沒有解決問題,試試搜尋本站其他內容

Sub-Query

SET

CTE (Common Table Expression)

發佈留言 取消回覆

發佈留言取消回覆