数据结构
网络
RDBMS
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 语言编程
C++
C #
MongoDB
MySQL
JavaScript
PHP
物理学
化学
生物学
数学
英语
经济学
心理学
社会学
时尚研究
法律研究在 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 -> ); Query OK, 0 rows affected (0.50 sec)
使用插入命令在表格中插入一些记录。查询如下
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)
使用 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)
更新于： 30-Jul-2019
519 浏览次数
广告