7
sql中使用LIMIT和OFFSET分页查询例子
source link: http://huanyouchen.github.io/2019/09/11/SQL-sample-paging-queries-using-LIMIT-and-OFFSET/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
幻悠尘的小窝
The quieter you become,the more you are able to hear.
sql中使用LIMIT和OFFSET分页查询例子
LIMIT 1 OFFSET 1
,其中OFFSET表示从哪里往后开始取,LIMIT表示取多少个。这里是从1往后开始取,取1个,即取第二高薪水的。
需要注意的是LIMIT m OFFSET n
和LIMIT m,n
的区别:
- LIMIT m OFFSET n,表示从第n个数往后开始取,取m个
LIMIT m,n ,等同于 LIMIT n OFFSET m, 表示从第m个数往后开始取,取n个
LIMIT OFFSET 测试例子:
mysql> SELECT * FROM employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
| 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
+--------+------------+------------+-----------+--------+------------+
11 rows in set (0.07 sec)
mysql> SELECT * FROM employees LIMIT 2,3;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM employees LIMIT 2 OFFSET 3;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM employees LIMIT 3 OFFSET 2;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
3 rows in set (0.00 sec)
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK