SQL TOP 子句用于从表中获取TOP N号或X%记录.
注意号;所有数据库都不支持TOP子句.例如,MySQL支持 LIMIT 子句获取有限数量的记录,而Oracle使用 ROWNUM 命令获取有限数量的记录.
带有SELECT语句的TOP子句的基本语法如下:
SELECT TOP number | percent column_name(s) FROM table_name WHERE [condition]
考虑具有以下记录的CUSTOMERS表 :
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
以下查询是SQL服务器上的一个示例,它将从CUSTOMERS表中获取前3个记录.
SQL> SELECT TOP 3 * FROM CUSTOMERS;
这会产生以下结果 :
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+
如果你使用的是MySQL服务器,那么这是一个等效的例子 :
SQL> SELECT * FROM CUSTOMERS LIMIT 3;
这会产生以下结果 :
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+
如果您使用的是Oracle服务器,则以下代码块具有相同的示例.
SQL> SELECT * FROM CUSTOMERS WHERE ROWNUM< = 3;
这会产生以下结果 :
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+