子查詢 (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)
沒有解決問題,試試搜尋本站其他內容