有一个类似于 SELECT * FROM clients ORDER BY id
的查询语句。我想只选择前10个元素。我该怎么做呢?
附注:我正在使用MySQL。
SELECT * FROM clients ORDER BY id LIMIT 10;
试试这个:
SELECT TOP 10 * FROM clients ORDER BY id
以下是可以使用SELECT语句执行的所有操作(摘自这里):
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]
因此,您需要的语句是:
SELECT * FROM clients ORDER BY id LIMIT 10
MySQL 的做法是使用:
SELECT * FROM clients ORDER BY id LIMIT 10;
这是MySQL特有的。长期以来,在其他数据库中没有相应的对应项,但是SQL:2008标准引入了一种额外的语法:
SELECT * FROM clients FETCH FIRST 10 ROWS ONLY;
并且:
SELECT * FROM clients OFFSET 1 FETCH NEXT 10 ROWS ONLY;
但问题是,这种语法目前还不被MySQL和大多数其他数据库支持。如果您关心可移植性,应该跟进开发情况。
请注意,您应该始终使用ORDER BY
子句,否则结果可能在不同的调用中随机出现。
ORDER BY id DESC
。 - Sampson