选择MySQL中升序排列的最后20条记录？

若要按升序选择最后20条记录，可以使用子查询LIMIT子句。语法如下

SELECT *FROM
(
   SELECT *FROM yourTableName ORDER BY yourColumnName desc limit 20
)
anyVariableName order by anyVariableName.yourColumnName;

为了理解上述语法，让我们创建一个表。创建表的查询如下

mysql> create table ProductInformation
   -> (
   -> ProductId int,
   -> ProductName varchar(100),
   -> ProductPrice int
   -> );

使用insert命令在表中插入一些记录。查询如下

mysql> insert into ProductInformation values(101,'Product-1',200);

mysql> insert into ProductInformation values(102,'Product-2',300);

mysql> insert into ProductInformation values(103,'Product-3',700);

mysql> insert into ProductInformation values(104,'Product-4',100);

mysql> insert into ProductInformation values(105,'Product-5',1500);

mysql> insert into ProductInformation values(106,'Product-6',1200);

mysql> insert into ProductInformation values(107,'Product-7',1300);

mysql> insert into ProductInformation values(108,'Product-8',1600);

mysql> insert into ProductInformation values(109,'Product-9',1250);

mysql> insert into ProductInformation values(110,'Product-10',1900);

mysql> insert into ProductInformation values(111,'Product-11',1870);

mysql> insert into ProductInformation values(112,'Product-12',1876);

mysql> insert into ProductInformation values(113,'Product-13',1869);

mysql> insert into ProductInformation values(114,'Product-14',1456);

mysql> insert into ProductInformation values(115,'Product-15',1860);

mysql> insert into ProductInformation values(116,'Product-16',359);

mysql> insert into ProductInformation values(117,'Product-17',1667);

mysql> insert into ProductInformation values(118,'Product-18',1467);

mysql> insert into ProductInformation values(119,'Product-19',2134);

mysql> insert into ProductInformation values(120,'Product-20',3450);

mysql> insert into ProductInformation values(121,'Product-21',198);

mysql> insert into ProductInformation values(122,'Product-22',195);

mysql> insert into ProductInformation values(123,'Product-23',10000);

使用select语句显示表中的所有记录。查询如下

mysql> select *from ProductInformation;

以下是输出

+-----------+-------------+--------------+
| 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)

这是查询以升序从表中选择最后20条记录

mysql> select *from
   -> (
   -> select *from ProductInformation order by ProductId desc limit 20
   -> ) t1 order by t1.ProductId asc;

以下是输出

+-----------+-------------+--------------+
| 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)

如果您希望记录按降序排列，请使用desc。查询如下，以降序获取结果。

mysql> select *from
   -> (
   -> select *from ProductInformation order by ProductId desc limit 20
   -> ) t2 order by t2.ProductId desc;

以下是输出

+-----------+-------------+--------------+
| 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)