SQL SELECT TOP, LIMIT, ROWNUM 子句

SQL SELECT TOP, LIMIT, ROWNUM 子句

SQL SELECT TOP 子句

SELECT TOP 语句用于在 SQL 中限制返回的结果集中的行数， 它通常用于只需要查询前几行数据的情况，尤其在数据集非常大时，可以显著提高查询性能。

SELECT TOP 子句对于拥有数千条记录的大型表来说，是非常有用的。

说明：

• SELECT TOP在 SQL Server 和 MS Access 中使用，而在 MySQL 和 PostgreSQL 中使用LIMIT关键字。
• Oracle 在 12c 版本之前没有直接等效的关键字，可以通过ROWNUM实现类似功能，但在 12c 及以上版本中引入了FETCH FIRST。
• 当使用TOP或LIMIT时，最好结合ORDER BY子句，以确保返回的行是特定顺序的前几行。

SQL Server / MS Access 语法

SELECT TOP number|percent column1, column2, ... FROM table_name;

number|percent：指定返回的行数或百分比。

• number：具体的行数。
• percent：数据集的百分比。

MySQL 语法

SELECT column1, column2, ... FROM table_name LIMIT number;

Oracle 语法

SELECT column1, column2, ... FROM table_name FETCH FIRST number ROWS ONLY;

PostgreSQL 语法

SELECT column1, column2, ... FROM table_name LIMIT number;

实例

假设我们有一个名为 Employees 的表，其中包含以下数据：

SQL Server 和 MS Access 返回前 3 行数据：

SELECT TOP 3 EmployeeName, Salary FROM Employees;

返回前 10% 的数据：

SELECT TOP 10 PERCENT EmployeeName, Salary FROM Employees;

MySQL 返回前 3 行数据：

SELECT EmployeeName, Salary FROM Employees LIMIT 3;

PostgreSQL 返回前 3 行数据：

SELECT EmployeeName, Salary FROM Employees LIMIT 3;

Oracle 返回前 3 行数据：

SELECT EmployeeName, Salary FROM Employees FETCH FIRST 3 ROWS ONLY;

演示数据库

在本教程中，我们将使用 RUNOOB 样本数据库。

下面是选自 "Websites" 表的数据：

mysql> SELECT * FROM Websites; +----+---------------+---------------------------+-------+---------+ | id | name | url | alexa | country | +----+---------------+---------------------------+-------+---------+ | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 3 | 菜鸟教程 | http://www.runoob.com/ | 5000 | USA | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | | 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND | +----+---------------+---------------------------+-------+---------+

MySQL SELECT LIMIT 实例

下面的 SQL 语句从 "Websites" 表中选取头两条记录：

实例

SELECT * FROM Websites LIMIT 2;

执行以上 SQL，数据如下所示：

vk.com/topic-234777923_54531860
vk.com/topic-234777846_54602244
vk.com/topic-234766022_54902544
vk.com/topic-234777924_54241464
vk.com/topic-234252226_54602243
vk.com/topic-234762548_54732708
vk.com/topic-234756700_55335763
vk.com/topic-234777584_54241463
vk.com/topic-234777928_54732707
vk.com/topic-234777908_54732706
vk.com/topic-234756911_55830487
vk.com/topic-234466620_55335762
vk.com/topic-234777957_55566538
vk.com/topic-234777876_54602242
vk.com/topic-234777886_54602241
vk.com/topic-234777901_55830486
vk.com/topic-234777913_54531859
vk.com/topic-234777903_54531858
vk.com/topic-234777875_54124700
vk.com/topic-234756933_54531857
vk.com/topic-234777887_55566537
vk.com/topic-234777923_54531856
vk.com/topic-234777924_54241462
vk.com/topic-234252226_54602240
vk.com/topic-234756700_55335759
vk.com/topic-234777584_54241461
vk.com/topic-234756911_55830485
vk.com/topic-234777886_54602239
vk.com/topic-234777901_55830484
vk.com/topic-234777927_55566536
vk.com/topic-234466620_55335760
vk.com/topic-234777957_55566535
vk.com/topic-234777875_54124699
vk.com/topic-234466708_54732704
vk.com/topic-234466423_54531854
vk.com/topic-234250764_54241460
vk.com/topic-234756933_54531853
vk.com/topic-234777843_54531852
vk.com/topic-234777952_54902543
vk.com/topic-234777923_54531851
vk.com/topic-234761493_54531850
vk.com/topic-234777924_54241459
vk.com/topic-234761470_55335758
vk.com/topic-234777905_54124697
vk.com/topic-234777584_54241458
vk.com/topic-234777876_54602238
vk.com/topic-234756911_55830482
vk.com/topic-234777888_54732702
vk.com/topic-234777927_55566533
vk.com/topic-234466708_54732701
vk.com/topic-234466423_54531849
vk.com/topic-234466620_55335757
vk.com/topic-234762356_54602237
vk.com/topic-234250764_54241457
vk.com/topic-234777912_54902541
vk.com/topic-234777937_55566532
vk.com/topic-234777843_54531848
vk.com/topic-234777952_54902540
vk.com/topic-234761493_54531847
vk.com/topic-234777924_54241456
vk.com/topic-234761472_54902539
vk.com/topic-234761470_55335756
vk.com/topic-234251298_54732699
vk.com/topic-234777905_54124696
vk.com/topic-234777584_54241455
vk.com/topic-234777876_54602236
vk.com/topic-234777901_55830481
vk.com/topic-234756911_55830480
vk.com/topic-234777927_55566530
vk.com/topic-234777888_54732698
vk.com/topic-234466423_54531846
vk.com/topic-234762356_54602235
vk.com/topic-234777937_55566529
vk.com/topic-234777912_54902538
vk.com/topic-234250764_54241454
vk.com/topic-234466620_55335755
vk.com/topic-234777843_54531845
vk.com/topic-234777952_54902537
vk.com/topic-234466708_54732697
vk.com/topic-234761472_54902536
vk.com/topic-234761470_55335754
vk.com/topic-234761493_54531844
vk.com/topic-234777877_55566528
vk.com/topic-234777913_54531843
vk.com/topic-234251298_54732696
vk.com/topic-234766256_54602234
vk.com/topic-234777905_54124695
vk.com/topic-234777925_54124694
vk.com/topic-234777907_55566527
vk.com/topic-234777971_55830479
vk.com/topic-234756911_55830478
vk.com/topic-234762356_54602233
vk.com/topic-234250764_54241453
vk.com/topic-234777912_54902535
vk.com/topic-234777937_55566526
vk.com/topic-234777952_54902534
vk.com/topic-234777843_54531842
vk.com/topic-234766022_54902533
vk.com/topic-234466708_54732695
vk.com/topic-234777891_55830477
vk.com/topic-234761472_54902532
vk.com/topic-234761493_54531841
vk.com/topic-234777877_55566525
vk.com/topic-234777913_54531840
vk.com/topic-234251298_54732694
vk.com/topic-234762548_54732693
vk.com/topic-234766256_54602232
vk.com/topic-234777905_54124692
vk.com/topic-234777925_54124691
vk.com/topic-234777907_55566524
vk.com/topic-234777971_55830476
vk.com/topic-234777897_55566523
vk.com/topic-234777901_55830475
vk.com/topic-234777908_54732692
vk.com/topic-234777787_55566522
vk.com/topic-234762356_54602231
vk.com/topic-234777846_54602230
vk.com/topic-234250764_54241451
vk.com/topic-234777937_55566521
vk.com/topic-234777912_54902530
vk.com/topic-234777952_54902529
vk.com/topic-234777843_54531837
vk.com/topic-234466708_54732691
vk.com/topic-234777891_55830473
vk.com/topic-234766022_54902528
vk.com/topic-234761472_54902527
vk.com/topic-234777877_55566520
vk.com/topic-234761493_54531834
vk.com/topic-234777928_54732690
vk.com/topic-234777913_54531836
vk.com/topic-234466429_54979489
vk.com/topic-234251298_54732689
vk.com/topic-234766256_54602229
vk.com/topic-234756700_55335751
vk.com/topic-234762548_54732688
vk.com/topic-234777925_54124690
vk.com/topic-234777883_54531833
vk.com/topic-234777907_55566519
vk.com/topic-234777876_54602228
vk.com/topic-234777905_54124689
vk.com/topic-234777971_55830472
vk.com/topic-234777897_55566517
vk.com/topic-234777901_55830471
vk.com/topic-234777957_55566516
vk.com/topic-234777908_54732687
vk.com/topic-234777875_54124688
vk.com/topic-234777787_55566515
vk.com/topic-234777886_54602227
vk.com/topic-234777887_55566514
vk.com/topic-234762356_54602226
vk.com/topic-234777846_54602225
vk.com/topic-234777937_55566513
vk.com/topic-234777912_54902525
vk.com/topic-234777952_54902524
vk.com/topic-234252226_54602224
vk.com/topic-234777891_55830470
vk.com/topic-234777903_54531832
vk.com/topic-234766022_54902523
vk.com/topic-234761472_54902522
vk.com/topic-234777927_55566512
vk.com/topic-234777923_54531831
vk.com/topic-234762422_54902521
vk.com/topic-234756933_54531830
vk.com/topic-234777877_55566511
vk.com/topic-234777928_54732686
vk.com/topic-234466429_54979488
vk.com/topic-234762548_54732685
vk.com/topic-234766256_54602223
vk.com/topic-234756700_55335750
vk.com/topic-234251298_54732684
vk.com/topic-234777925_54124687
vk.com/topic-234777883_54531829
vk.com/topic-234777907_55566510
vk.com/topic-234777876_54602222
vk.com/topic-234777971_55830469
vk.com/topic-234777897_55566509
vk.com/topic-234777957_55566508
vk.com/topic-234777908_54732683
vk.com/topic-234777875_54124686
vk.com/topic-234777787_55566507
vk.com/topic-234777886_54602221
vk.com/topic-234777887_55566506
vk.com/topic-234777846_54602220
vk.com/topic-234777952_54902520
vk.com/topic-234777937_55566505
vk.com/topic-234252226_54602219
vk.com/topic-234466708_54732682
vk.com/topic-234777891_55830468
vk.com/topic-234777903_54531828
vk.com/topic-234766022_54902519
vk.com/topic-234777927_55566504
vk.com/topic-234777923_54531827
vk.com/topic-234777877_55566502
vk.com/topic-234756933_54531826
vk.com/topic-234777928_54732681
vk.com/topic-234466429_54979487
vk.com/topic-234762548_54732680
vk.com/topic-234756700_55335749
vk.com/topic-234766256_54602218

SQL SELECT TOP PERCENT 实例

在 Microsoft SQL Server 中还可以使用百分比作为参数。

下面的 SQL 语句从 websites 表中选取前面百分之 50 的记录：

实例

以下操作在 Microsoft SQL Server 数据库中可执行。

SELECT TOP 50 PERCENT * FROM Websites;