SQL TOP子句用于從表中獲取一個(gè)TOP N數(shù)字或X%的紀(jì)錄。
注意: 不是所有的數(shù)據(jù)庫(kù)都支持TOP子句。例如,MySQL使用LIMIT子句來(lái)獲取記錄;Oracle使用ROWNUM獲取有限的記錄數(shù)。
SELECT語(yǔ)句TOP子句的基本語(yǔ)法將如下所示:
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 SERVER服務(wù)器,這將取前客戶表的前3個(gè)記錄的例子:
SQL> SELECT TOP 3 * FROM CUSTOMERS;
這將產(chǎn)生以下結(jié)果:
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+
如果你使用MySQL服務(wù)器,然后在這里是一個(gè)等效的例子:
SQL> SELECT * FROM CUSTOMERS LIMIT 3;
這將產(chǎn)生以下結(jié)果:
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+
如果您使用Oracle服務(wù)器,然后在這里是一個(gè)等效的例子:
SQL> SELECT * FROM CUSTOMERS WHERE ROWNUM <= 3;
這將產(chǎn)生以下結(jié)果:
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad