MariaDB – LIMIT row_count OFFSET offset

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

要找出最大值的時候可以透過 MAX 或是 LIMIT 1,但是要找第二,三 … 的值呢? 可以透過 LIMIT

LIMIT offset, row_count
LIMIT row_count OFFSET offset

先建立一個資料庫 (testdb) , 與 tables (Employee) 格式為 ID INT(11) 以及 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) ,Salary int(11));
Query OK, 0 rows affected (0.003 sec)

MariaDB [testdb]> INSERT INTO Employee VALUES (1,1000) ,(2,2000) , (3,3000) , (4,4000) , (5 ,5000);
Query OK, 5 rows affected (0.001 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM Employee;
+------+--------+
| ID   | Salary |
+------+--------+
|    1 |   1000 |
|    2 |   2000 |
|    3 |   3000 |
|    4 |   4000 |
|    5 |   5000 |
+------+--------+
5 rows in set (0.000 sec)

找出最大值.

MariaDB [testdb]> SELECT * FROM Employee ORDER BY Salary DESC LIMIT 1;
+------+--------+
| ID   | Salary |
+------+--------+
|    5 |   5000 |
+------+--------+
1 row in set (0.000 sec)

找出第二最大值.

MariaDB [testdb]> SELECT * FROM Employee ORDER BY Salary DESC LIMIT 1,1;
+------+--------+
| ID   | Salary |
+------+--------+
|    4 |   4000 |
+------+--------+
1 row in set (0.000 sec)

或是

MariaDB [testdb]> SELECT * FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1;
+------+--------+
| ID   | Salary |
+------+--------+
|    4 |   4000 |
+------+--------+
1 row in set (0.000 sec)

找出第二,三最大值.

MariaDB [testdb]> SELECT * FROM Employee ORDER BY Salary DESC LIMIT 1,2;
+------+--------+
| ID   | Salary |
+------+--------+
|    4 |   4000 |
|    3 |   3000 |
+------+--------+
2 rows in set (0.001 sec)