MariaDB – Correlated Subqueries

測試環境為 CentOS 8 X86_64 (虛擬機)

怎麼在 GROUP 時,找到 第二,三 … 的值.

先建立一個資料庫 (testdb) , 與 tables (employee) 格式為 Name char(20), Dept char(20), jobTitle char(20) 各 20 個字元以及 Salary int(11).

[root@localhost ~]# mysql -u root -p
Enter password:
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE testdb;
Database changed

MariaDB [testdb]> CREATE TABLE Employee (ID INT(11) NOT NULL AUTO_INCREMENT , Name char(20) , Dept char(20) , JobTitle char(20) , Salary int(1
1) , PRIMARY KEY (ID));
Query OK, 0 rows affected (0.003 sec)

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

MariaDB [testdb]> INSERT INTO Employee (Name , Dept , JobTitle , Salary) VALUES ('Ben','Testing','Engineer','45000') , ('Afa','Power','Enginee
r','48000') , ('Boss','Testing','Manager','75000') , ('Cars','Testing','Senior Engineer','42000');
Query OK, 4 rows affected (0.001 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM Employee;
+----+------+---------+-----------------+--------+
| ID | Name | Dept    | JobTitle        | Salary |
+----+------+---------+-----------------+--------+
|  1 | Ben  | Testing | Engineer        |  45000 |
|  2 | Afa  | Power   | Engineer        |  48000 |
|  3 | Boss | Testing | Manager         |  75000 |
|  4 | Cars | Testing | Senior Engineer |  42000 |
+----+------+---------+-----------------+--------+
4 rows in set (0.000 sec)

找出該單位 Salary 最大值.

MariaDB [testdb]> SELECT Dept , MAX(Salary) FROM Employee GROUP BY Dept;
+---------+-------------+
| Dept    | MAX(Salary) |
+---------+-------------+
| Power   |       48000 |
| Testing |       75000 |
+---------+-------------+
2 rows in set (0.000 sec)

透過 Correlated Subqueries 來查詢 第二,三 … 大的值.

找出該單位 Salary 第二最大值.Power 單位只有一個人,所以第二大值為 NULL.

MariaDB [testdb]> SELECT Dept , (SELECT e1.Salary FROM Employee e1 WHERE e1.Dept=e.Dept ORDER BY e1.Salary DESC LIMIT 1,1) AS 'Second High Salary' FROM Employee e GROUP BY Dept;
+---------+--------------------+
| Dept    | Second High Salary |
+---------+--------------------+
| Power   |               NULL |
| Testing |              45000 |
+---------+--------------------+
2 rows in set (0.001 sec)

找出該單位 Salary 第三最大值.Power 單位只有一個人,所以第三大值為 NULL.

MariaDB [testdb]> SELECT Dept , (SELECT e1.Salary FROM Employee e1 WHERE e1.Dept=e.Dept ORDER BY e1.Salary DESC LIMIT 2,1) AS 'Second High Salary' FROM Employee e GROUP BY Dept;
+---------+--------------------+
| Dept    | Second High Salary |
+---------+--------------------+
| Power   |               NULL |
| Testing |              42000 |
+---------+--------------------+
2 rows in set (0.001 sec)