Để chọn 20 bản ghi cuối cùng theo thứ tự tăng dần, bạn có thể sử dụng mệnh đề LIMIT truy vấn con. Cú pháp như sau
SELECT *FROM ( SELECT *FROM yourTableName ORDER BY yourColumnName desc limit 20 ) anyVariableName order by anyVariableName.yourColumnName;
Để hiểu cú pháp trên, chúng ta hãy tạo một bảng. Truy vấn để tạo bảng như sau
mysql> create table ProductInformation -> ( -> ProductId int, -> ProductName varchar(100), -> ProductPrice int -> ); Query OK, 0 rows affected (0.50 sec)
Chèn một số bản ghi trong bảng bằng lệnh chèn. Truy vấn như sau
mysql> insert into ProductInformation values(101,'Product-1',200); Query OK, 1 row affected (0.16 sec) mysql> insert into ProductInformation values(102,'Product-2',300); Query OK, 1 row affected (0.23 sec) mysql> insert into ProductInformation values(103,'Product-3',700); Query OK, 1 row affected (0.09 sec) mysql> insert into ProductInformation values(104,'Product-4',100); Query OK, 1 row affected (0.15 sec) mysql> insert into ProductInformation values(105,'Product-5',1500); Query OK, 1 row affected (0.18 sec) mysql> insert into ProductInformation values(106,'Product-6',1200); Query OK, 1 row affected (0.18 sec) mysql> insert into ProductInformation values(107,'Product-7',1300); Query OK, 1 row affected (0.17 sec) mysql> insert into ProductInformation values(108,'Product-8',1600); Query OK, 1 row affected (0.29 sec) mysql> insert into ProductInformation values(109,'Product-9',1250); Query OK, 1 row affected (0.15 sec) mysql> insert into ProductInformation values(110,'Product-10',1900); Query OK, 1 row affected (0.15 sec) mysql> insert into ProductInformation values(111,'Product-11',1870); Query OK, 1 row affected (0.13 sec) mysql> insert into ProductInformation values(112,'Product-12',1876); Query OK, 1 row affected (0.11 sec) mysql> insert into ProductInformation values(113,'Product-13',1869); Query OK, 1 row affected (0.19 sec) mysql> insert into ProductInformation values(114,'Product-14',1456); Query OK, 1 row affected (0.25 sec) mysql> insert into ProductInformation values(115,'Product-15',1860); Query OK, 1 row affected (0.16 sec) mysql> insert into ProductInformation values(116,'Product-16',359); Query OK, 1 row affected (0.21 sec) mysql> insert into ProductInformation values(117,'Product-17',1667); Query OK, 1 row affected (0.09 sec) mysql> insert into ProductInformation values(118,'Product-18',1467); Query OK, 1 row affected (0.11 sec) mysql> insert into ProductInformation values(119,'Product-19',2134); Query OK, 1 row affected (0.24 sec) mysql> insert into ProductInformation values(120,'Product-20',3450); Query OK, 1 row affected (0.10 sec) mysql> insert into ProductInformation values(121,'Product-21',198); Query OK, 1 row affected (0.22 sec) mysql> insert into ProductInformation values(122,'Product-22',195); Query OK, 1 row affected (0.21 sec) mysql> insert into ProductInformation values(123,'Product-23',10000); Query OK, 1 row affected (0.15 sec)
Hiển thị tất cả các bản ghi từ bảng bằng cách sử dụng câu lệnh select. Truy vấn như sau
mysql> select *from ProductInformation;
Sau đây là kết quả
+-----------+-------------+--------------+ | ProductId | ProductName | ProductPrice | +-----------+-------------+--------------+ | 101 | Product-1 | 200 | | 102 | Product-2 | 300 | | 103 | Product-3 | 700 | | 104 | Product-4 | 100 | | 105 | Product-5 | 1500 | | 106 | Product-6 | 1200 | | 107 | Product-7 | 1300 | | 108 | Product-8 | 1600 | | 109 | Product-9 | 1250 | | 110 | Product-10 | 1900 | | 111 | Product-11 | 1870 | | 112 | Product-12 | 1876 | | 113 | Product-13 | 1869 | | 114 | Product-14 | 1456 | | 115 | Product-15 | 1860 | | 116 | Product-16 | 359 | | 117 | Product-17 | 1667 | | 118 | Product-18 | 1467 | | 119 | Product-19 | 2134 | | 120 | Product-20 | 3450 | | 121 | Product-21 | 198 | | 122 | Product-22 | 195 | | 123 | Product-23 | 10000 | +-----------+-------------+--------------+ 23 rows in set (0.00 sec)
Đây là truy vấn để chọn 20 bản ghi cuối cùng từ bảng theo thứ tự tăng dần
mysql> select *from -> ( -> select *from ProductInformation order by ProductId desc limit 20 -> ) t1 order by t1.ProductId asc;
Sau đây là kết quả
+-----------+-------------+--------------+ | ProductId | ProductName | ProductPrice | +-----------+-------------+--------------+ | 104 | Product-4 | 100 | | 105 | Product-5 | 1500 | | 106 | Product-6 | 1200 | | 107 | Product-7 | 1300 | | 108 | Product-8 | 1600 | | 109 | Product-9 | 1250 | | 110 | Product-10 | 1900 | | 111 | Product-11 | 1870 | | 112 | Product-12 | 1876 | | 113 | Product-13 | 1869 | | 114 | Product-14 | 1456 | | 115 | Product-15 | 1860 | | 116 | Product-16 | 359 | | 117 | Product-17 | 1667 | | 118 | Product-18 | 1467 | | 119 | Product-19 | 2134 | | 120 | Product-20 | 3450 | | 121 | Product-21 | 198 | | 122 | Product-22 | 195 | | 123 | Product-23 | 10000 | +-----------+-------------+--------------+ 20 rows in set (0.00 sec)
Nếu bạn muốn các bản ghi theo thứ tự giảm dần, thì hãy sử dụng mô tả. Truy vấn như sau để nhận kết quả theo thứ tự giảm dần.
mysql> select *from -> ( -> select *from ProductInformation order by ProductId desc limit 20 -> ) t2 order by t2.ProductId desc;
Sau đây là kết quả
+-----------+-------------+--------------+ | ProductId | ProductName | ProductPrice | +-----------+-------------+--------------+ | 123 | Product-23 | 10000 | | 122 | Product-22 | 195 | | 121 | Product-21 | 198 | | 120 | Product-20 | 3450 | | 119 | Product-19 | 2134 | | 118 | Product-18 | 1467 | | 117 | Product-17 | 1667 | | 116 | Product-16 | 359 | | 115 | Product-15 | 1860 | | 114 | Product-14 | 1456 | | 113 | Product-13 | 1869 | | 112 | Product-12 | 1876 | | 111 | Product-11 | 1870 | | 110 | Product-10 | 1900 | | 109 | Product-9 | 1250 | | 108 | Product-8 | 1600 | | 107 | Product-7 | 1300 | | 106 | Product-6 | 1200 | | 105 | Product-5 | 1500 | | 104 | Product-4 | 100 | +-----------+-------------+--------------+ 20 rows in set (0.00 sec)