SQL 語法 Sub-Query

子查詢 (sub-query) 為 一段 SQL 語法中內還包含一段 SELECT , Sub-Query SELECT 敘述需置於 左右刮號() 中.

測試環境為 CentOS 8 x86_64 (虛擬機)
先建立一個資料庫 (testdb) , 與 tables (employee) 格式為 Name VARCHAR(20), Dept VARCHAR(20), jobTitle VARCHAR(20) 各 20 個字元以及 數字欄位 Age INT(3) , Seniority INT(3) , Salary INT(11).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-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.001 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (Name VARCHAR(20) , Dept VARCHAR(20) , JobTitle VARCHAR(20) ,Age INT(3) , Seniority INT(3) , Salary INT(11));
Query OK, 0 rows affected (0.009 sec)

MariaDB [testdb]> DESCRIBE employee; 
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Name      | varchar(20) | YES  |     | NULL    |       |
| Dept      | varchar(20) | YES  |     | NULL    |       |
| JobTitle  | varchar(20) | YES  |     | NULL    |       |
| Age       | int(3)      | YES  |     | NULL    |       |
| Seniority | int(3)      | YES  |     | NULL    |       |
| Salary    | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.005 sec)

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

MariaDB [testdb]> INSERT INTO employee VALUES ('Ben' , 'Testing' , 'Engineer' , '32' , '5' , '45000') , ('Afa' , 'Power' , 'Engineer' , '28' , '2' , '48000') , ('Boss' , 'Testing' , 'Manager' , '42' , '8' , '75000') , ('Justin' , 'Testing' , 'Senior Engineer' , ' 26' , '2' , '42000') , ('Jasmine' , 'Power' , 'Manager' , '41' , '9' ,'80000') ;
Query OK, 5 rows affected (0.002 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM employee;
+---------+---------+-----------------+------+-----------+--------+
| Name    | Dept    | JobTitle        | Age  | Seniority | Salary |
+---------+---------+-----------------+------+-----------+--------+
| Ben     | Testing | Engineer        |   32 |         5 |  45000 |
| Afa     | Power   | Engineer        |   28 |         2 |  48000 |
| Boss    | Testing | Manager         |   42 |         8 |  75000 |
| Justin  | Testing | Senior Engineer |   26 |         2 |  42000 |
| Jasmine | Power   | Manager         |   41 |         9 |  80000 |
+---------+---------+-----------------+------+-----------+--------+
5 rows in set (0.001 sec)

Sub-Query 可以放在 SELECT , FROM 以及 WHERE 後面的敘述.

SELECT col1 , col2
FROM sample_table
WHERE col1 < 0;

SELECT

計算出個單位薪水的百分比,主要先算出總薪水 SELECT SUM(Salary) FROM employee 然後去除以個別單位的薪水總合..

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 |
+---------+-------------+------------+
| Power   |      128000 | 44.13%     |
| Testing |      162000 | 55.86%     |
| Total   |      290000 | 100.00%    |
+---------+-------------+------------+
3 rows in set, 1 warning (0.005 sec)

或是另將總薪水以變數儲存起來 @Total:=(SELECT SUM(Salary) FROM employee) ,後面再拿來利用.

MariaDB [testdb]> SELECT IFNULL(Dept,"Total") AS DEPT ,SUM(Salary) AS "DEPT Salary", @Total:=(SELECT SUM(Salary) FROM employee) AS Total, CONCAT(TRUNCATE(SUM(Salary)*100/@Total,2),"%") AS "Percentage" FROM employee GROUP BY Dept WITH ROLLUP;
+---------+-------------+--------+------------+
| DEPT    | DEPT Salary | Total  | Percentage |
+---------+-------------+--------+------------+
| Power   |      128000 | 290000 | 44.13%     |
| Testing |      162000 | 290000 | 55.86%     |
| Total   |      290000 | 290000 | 100.00%    |
+---------+-------------+--------+------------+
3 rows in set, 1 warning (0.001 sec)

函數說明:

  • IFNULL
    IFNULL(Expression, ALTER_Value) 用來判斷 Expression 字串是否為 NULL ,是就以 ALTER_Value 來取代.
  • SUM
    計算數字加總.
  • CONCAT
    CONTACT 函數可以將兩個或更多個字串連接在一起.
  • TRUNCATE
    取小數點,不計算四捨五入.

FROM

我們先 查詢 Dept=’testing’ ,再從這資料中查詢 Salary>50000 的欄位.

MariaDB [testdb]> SELECT * FROM (SELECT * FROM employee WHERE Dept='testing') testing WHERE Salary>50000;
+------+---------+----------+------+-----------+--------+
| Name | Dept    | JobTitle | Age  | Seniority | Salary |
+------+---------+----------+------+-----------+--------+
| Boss | Testing | Manager  |   42 |         8 |  75000 |
+------+---------+----------+------+-----------+--------+
1 row in set (0.001 sec)

WHERE

通常 EXISTS, NOT EXISTS 會搭配 Sub-query 一起使用, EXISTS 回傳 True 或是 False,用來決定該敘述是否要執行.

先確定薪水有超過 100000 , 50000 再來做查詢.

MariaDB [testdb]> SELECT * FROM employee WHERE EXISTS(SELECT Salary FROM employee WHERE Salary > 100000);
Empty set (0.001 sec)

MariaDB [testdb]> SELECT * FROM employee WHERE EXISTS(SELECT Salary FROM employee WHERE Salary > 50000);
+---------+---------+-----------------+------+-----------+--------+
| Name    | Dept    | JobTitle        | Age  | Seniority | Salary |
+---------+---------+-----------------+------+-----------+--------+
| Ben     | Testing | Engineer        |   32 |         5 |  45000 |
| Afa     | Power   | Engineer        |   28 |         2 |  48000 |
| Boss    | Testing | Manager         |   42 |         8 |  75000 |
| Justin  | Testing | Senior Engineer |   26 |         2 |  42000 |
| Jasmine | Power   | Manager         |   41 |         9 |  80000 |
+---------+---------+-----------------+------+-----------+--------+
5 rows in set (0.001 sec)

常見錯誤

MariaDB [testdb]> SELECT * FROM (SELECT * FROM employee WHERE Dept='testing') WHERE Salary>50000;
Error 1248 (42000): Every derived table must have its own alias

FROM 後面接 (SELECT) 需接 alias 名稱,改成如下即可.

MariaDB [testdb]> SELECT * FROM (SELECT * FROM employee WHERE Dept='testing') testing WHERE Salary>50000;
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Boss | Testing | Manager  |  75000 |
+------+---------+----------+--------+
1 row in set (0.00 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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