如何找到資料特定欄位中最大 Maximum 的一筆資料與進階應用-如何在分類 (GROUP BY) 好的資料中找出各別最大 Maximum 的資料.
測試環境為 CentOS 8 x86_64 (虛擬機)
先建立一個測試用資料庫 (testdb) , 與 tables (employee) 格式為 K1 int(11) – auto_increment & PRIMARY KEY , Name char(20), Dept char(20), jobTitle char(20) 各 20 個字元以及 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.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.
將資料庫字元編碼 ( Character Sets ) 設定為 utf8 (8-bit Unicode Transformation Format) 與 文字排序 ( Collations ) 設定為utf8_general_ci .
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, 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 | | +----------+----------+------+-----+---------+----------------+ 5 rows in set (0.005 sec)
透過 INSERT 新增加了四筆資料.
MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary) VALUES ('Ben','Testing','Engineer','45000') , ('Afa','Power','Engineer','48000') , ('Boss','Testing','Manager','75000') , ('Cars','Testing','Senior Engineer','42000');
Query OK, 4 rows affected (0.003 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [testdb]> SELECT * FROM employee;
+----+------+---------+-----------------+--------+
| K1 | 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.001 sec)
上敘的資料,要怎麼找到全公司或是部門中薪水 (Salary 數字欄位) 最高的.
全公司薪水 (Salary 數字欄位) 最高的
- SELECT ORDER BY LIMIT
如果是全公司薪水 (Salary 數字欄位) 最高的,相對簡單可以對查詢後的資料內容加上 排序 ( ORDER DESC ) 並限制 ( LIMIT ) 最高的一筆.MariaDB [testdb]> SELECT * FROM employee ORDER BY Salary DESC LIMIT 1; +----+------+---------+----------+--------+ | K1 | Name | Dept | JobTitle | Salary | +----+------+---------+----------+--------+ | 3 | Boss | Testing | Manager | 75000 | +----+------+---------+----------+--------+ 1 row in set (0.001 sec)
- ORDER BY 預設使用 ASC (Ascending) 從小到大,也可以設定從大到小 DESC (Descending).
- LIMIT 可以限制查詢回應的行數,使用方式有兩種 N (只需要前面 N 行的資料) 或是 N,M (只顯示從 N+1 到 M 行的資料).
- 子查詢 (sub-query)
子查詢 (sub-query) 為 一段 SQL 語法中內還包含一段 SELECT ( SELECT 敘述需置於 左右刮號 中).
先找出薪水最高的( MAX 函數會返回數值中最大的),然後依據這個數字查詢是在哪一個欄位,有了欄位就可以找出該筆資料(不知道有沒有更好的方式？).MariaDB [testdb]> SELECT * FROM employee WHERE Salary=(SELECT Max(salary) FROM employee); +----+------+---------+----------+--------+ | K1 | Name | Dept | JobTitle | Salary | +----+------+---------+----------+--------+ | 3 | Boss | Testing | Manager | 75000 | +----+------+---------+----------+--------+ 1 row in set (0.001 sec)
如果是要查詢各部門中薪水 (Salary 數字欄位) 最高的.
如果已經知道有哪個個部門,就可以分次查詢.
MariaDB [testdb]> SELECT * FROM employee WHERE Salary=(SELECT Max(salary) FROM employee WHERE Dept LIKE 'Testing'); +----+------+---------+----------+--------+ | K1 | Name | Dept | JobTitle | Salary | +----+------+---------+----------+--------+ | 3 | Boss | Testing | Manager | 75000 | +----+------+---------+----------+--------+ 1 row in set (0.001 sec)
MariaDB [testdb]> SELECT * FROM employee WHERE Salary=(SELECT Max(salary) FROM employee WHERE Dept LIKE 'Power'); +----+------+-------+----------+--------+ | K1 | Name | Dept | JobTitle | Salary | +----+------+-------+----------+--------+ | 2 | Afa | Power | Engineer | 48000 | +----+------+-------+----------+--------+ 1 row in set (0.001 sec)
或是利用 group-identifier, max-value-in-group 的 Sub-query 語法.
- Sub-query (group-identifier, max-value-in-group)
MariaDB [testdb]> SELECT employee.* FROM employee, (SELECT Dept , Max(salary) AS Salary FROM employee GROUP BY Dept) Max_Salary WHERE employee.Dept=Max_Salary.Dept AND employee.Salary=Max_Salary.Salary; +----+------+---------+----------+--------+ | K1 | Name | Dept | JobTitle | Salary | +----+------+---------+----------+--------+ | 2 | Afa | Power | Engineer | 48000 | | 3 | Boss | Testing | Manager | 75000 | +----+------+---------+----------+--------+ 2 rows in set (0.001 sec)
- Sub-query (group-identifier, max-value-in-group)
MariaDB [testdb]> SELECT * FROM employee WHERE (Dept , salary) IN (SELECT Dept , Max(salary) AS Salary FROM employee GROUP BY Dept); +----+------+---------+----------+--------+ | K1 | Name | Dept | JobTitle | Salary | +----+------+---------+----------+--------+ | 2 | Afa | Power | Engineer | 48000 | | 3 | Boss | Testing | Manager | 75000 | +----+------+---------+----------+--------+ 2 rows in set (0.001 sec)
- INNER JOIN + Sub-query (group-identifier, max-value-in-group)
MariaDB [testdb]> SELECT a.K1, a.Name, a.Dept , a.JobTitle , a.Salary FROM employee a INNER JOIN ( SELECT Dept , Max(salary) AS Salary FROM employee GROUP BY Dept ) b ON a.Dept = b.Dept AND a.Salary = b.Salary; +----+------+---------+----------+--------+ | K1 | Name | Dept | JobTitle | Salary | +----+------+---------+----------+--------+ | 2 | Afa | Power | Engineer | 48000 | | 3 | Boss | Testing | Manager | 75000 | +----+------+---------+----------+--------+ 2 rows in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容