选择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)