Chọn hàng trong khoảng (range) cụ thể với MySQL

Tạo bảng:

mysql> CREATE TABLE DemoTable ( -> Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName VARCHAR(20) -> ); Query OK, 0 rows affected (1.23 sec)

Chèn bản ghi:

mysql> INSERT INTO DemoTable(StudentName) VALUES('John'); Query OK, 1 row affected (0.08 sec) mysql> INSERT INTO DemoTable(StudentName) VALUES('Carol'); Query OK, 1 row affected (0.20 sec) mysql> INSERT INTO DemoTable(StudentName) VALUES('David'); Query OK, 1 row affected (0.07 sec) mysql> INSERT INTO DemoTable(StudentName) VALUES('Bob'); Query OK, 1 row affected (0.12 sec) mysql> INSERT INTO DemoTable(StudentName) VALUES('Chris'); Query OK, 1 row affected (0.12 sec) mysql> INSERT INTO DemoTable(StudentName) VALUES('Robert'); Query OK, 1 row affected (0.13 sec) mysql> INSERT INTO DemoTable(StudentName) VALUES('Adam'); Query OK, 1 row affected (0.15 sec) mysql> INSERT INTO DemoTable(StudentName) VALUES('Mike'); Query OK, 1 row affected (0.12 sec) mysql> INSERT INTO DemoTable(StudentName) VALUES('Sam'); Query OK, 1 row affected (0.16 sec)

Hiển thị:

mysql> SELECT * FROM DemoTable;

Kết quả

+----+-------------+ | Id | StudentName | +----+-------------+ | 1  | John        | | 2  | Carol       | | 3  | David       | | 4  | Bob         | | 5  | Chris       | | 6  | Robert      | | 7  | Adam        | | 8  | Mike        | | 9  | Sam         | +----+-------------+ 9 rows in set (0.00 sec)

Truy vấn chọn hàng cụ thể (4 hàng, bỏ qua 2 hàng đầu, sắp xếp giảm dần):

mysql> SELECT * FROM DemoTable ORDER BY Id DESC LIMIT 4 OFFSET 2;

Kết quả

+----+-------------+ | Id | StudentName | +----+-------------+ | 7  | Adam        | | 6  | Robert      | | 5  | Chris       | | 4  | Bob         | +----+-------------+ 4 rows in set (0.00 sec)