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

以上是 选择MySQL中升序排列的最后20条记录? 的全部内容, 来源链接: utcz.com/z/348772.html

回到顶部