Sql 简明教程
SQL - TOP Clause
The SQL TOP Clause
在从 SQL 表中检索数据时，SQL TOP clause 用于限制 SQL server 中 SELECT 查询返回的行数。此外，我们还可以在 UPDATE 和 DELETE 语句中使用它来限制（限制）结果记录。
While we are retrieving data from an SQL table, the SQL TOP clause is used to restrict the number of rows returned by a SELECT query in SQL server. In addition, we can also use it with UPDATE and DELETE statements to limit (restrict) the resultant records.
例如，如果你的数据库表中存储了大量数据，并且你只想对前 N 行执行操作，则可以在你的 SQL server 查询中使用 TOP 子句。
For instance, if you have a large number of data stored in a database table, and you only want to perform operations on first N rows, you can use the TOP clause in your SQL server query.
Syntax
SQL TOP 子句的基本语法如下 −
The basic syntax of the SQL TOP clause is as follows −
SELECT TOP value column_name(s)
FROM table_name
WHERE [condition]其中， value 是要返回的顶层行数/百分比。
Where, value is the number/ percentage of number of rows to return from the top.
Example
为了更好地理解它，让我们考虑一下 CUSTOMERS 表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和工资等，如下所示：
To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，使用如下所示的 INSERT 语句向此表中插入值：
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES
(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, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );该表将按如下方式创建：
The table will be created as follows −
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 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
现在，我们使用 TOP 子句来获取 CUSTOMERS 表中的前 4 条记录，而不指定任何条件子句，例如 WHERE、ORDER BY 等 −
Now, we are using the TOP clause to fetch the top 4 records from the CUSTOMERS table without specifying any conditional clauses such as WHERE, ORDER BY, etc. −
SELECT TOP 4 * FROM CUSTOMERS;TOP with ORDER BY Clause
SQL 中的 ORDER BY 子句用于按升序或降序对查询的结果集进行排序。我们可以将其与 TOP 子句一起使用，以按排序顺序检索（或更新或删除等）前 N 条记录。
The ORDER BY clause in SQL is used to sort the result-set of a query in ascending or descending order. We can use it along with the TOP clause to retrieve (or, update or, delete etc.) first N records in sorted order.
Example
使用以下查询，我们按排序顺序检索 CUSTOMERS 表的前 4 条记录。在此，我们根据 SALARY 列以降序对表进行排序 −
Using the following query we are retrieving the top 4 records of the CUSTOMERS table in a sorted order. Here, we are sorting the table in descending order based on the SALARY column −
SELECT TOP 4 * FROM CUSTOMERS ORDER BY SALARY DESC;Output
我们获得结果如下 −
We obtain the result as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
7 |
Muffy |
24 |
Indore |
10000.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
默认情况下，ORDER BY 子句按升序对数据进行排序。因此，如果需要按降序对数据进行排序，我们必须使用 DESC 关键字。
Note − By default, the ORDER BY clause sorts the data in ascending order. So, if we need to sort the data in descending order, we must use the DESC keyword.
TOP Clause with PERCENT
我们还可以使用 PERSENT 子句和 TOP 子句，通过指定百分比值而不是数字来限制记录。
We can also restrict the records by specifying percentage value instead of number, using the PERCENT clause along with the TOP clause.
Example
以下查询按 SALARY 在升序中对 CUSTOMERS 表中的前 40% 的记录进行选择 −
The following query selects the first 40% of the records from the CUSTOMERS table sorted in the ascending order by their SALARY −
SELECT TOP 40 PERCENT * FROM CUSTOMERS ORDER BY SALARYOutput
我们的表中总共有 7 条记录。因此，7 的 40% 是 2.8。因此，SQL 服务器将结果舍入为三行（下一个整数），如下面输出中所示 −
We have the total of 7 records in our table. So 40% of 7 is 2.8. Therefore, SQL server rounds the result to three rows (the next whole number) as shown in the output below −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
TOP with WHERE Clause
我们可以将 TOP 子句与 WHERE 子句一起使用，以限制给定的行数并根据指定条件对其进行筛选。
We can use the TOP clause with the WHERE clause to limit the given number of rows and filter them based on a specified condition.
TOP Clause With DELETE Statement
TOP 子句可与 DELETE 语句一起使用，以删除满足给定条件的特定行数。
The TOP clause can be used with the DELETE statement to delete a specific number of rows that meet the given criteria.
Example
在以下查询中，我们使用带 TOP 子句的 DELETE 语句。在此，我们删除 NAME 以 K 开头的 2 个顶层客户 −
In the following query, we are using DELETE statement with TOP clause. Here, we are deleting the top 2 customers whose NAME starts with K −
DELETE TOP(2) FROM CUSTOMERS WHERE NAME LIKE 'K%';Verification
我们可以使用 SELECT 语句检索表内容来验证表中是否反映了更改，如下所示 −
We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as shown below −
SELECT * FROM CUSTOMERS;该表显示如下：
The table is displayed as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
TOP and WITH TIES Clause
使用 ORDER BY 子句根据列对表中的数据进行排序时，某些情况下，多行在 ORDER BY 子句中指定的列中可能包含相同的值。
While sorting the data in a table using the ORDER BY clause based on a column, some times multiple rows may contain same values in the column(s) specified in the ORDER BY clause.
如果尝试使用 TOP 子句限制记录数，则并非所有符合条件的列都可能被筛选。
If you try to restrict the number of records using the TOP clause, all the eligible columns may not be filtered.
WITH TIES 子句用于确保具有相同值（具有 "tied" 值的记录）的记录包含在查询结果中。
The WITH TIES clause is used to ensure that the records having the same values (records with "tied" values) are included in the query results.
Example
考虑以上创建的表 CUSTOMERS 。如果我们需要根据工资值按升序排列检索前 2 位客户，则查询应如下所示：−
Consider the above created table CUSTOMERS. If we need to retrieve the top 2 customers sorted by the ascending order of their SALARY values, the query would be −
SELECT TOP 2 * FROM CUSTOMERS ORDER BY SALARY;结果表如下：−
The resultant table would be −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
但是，表中前两个工资值（按升序排列）是 1500 和 2000，并且 CUSTOMERS 表中还有另一列工资值为 2000 的列，此列未包含在结果中。
But, the first two salary values (in ascending order) in the table are 1500 and 2000 and there is another column in the CUSTOMERS table with salary value 2000 which is not included in the result.
如果要检索前两个工资值（按升序排列）的所有列。我们需要使用 WITH TIES 子句，如下所示：−
If you want to retrieve all the columns with first two salary values (when arranged in the ascending order). We need to use the WITH TIES clause as showb below −
SELECT TOP 2 WITH TIES * FROM CUSTOMERS ORDER BY SALARY;Uses of TOP Clause
TOP 子句的一些常见用例包括：−
Some common use cases for the TOP clause include −
-
Pagination − When displaying a large number of records, the TOP clause can be used to return only a certain number of records at a time, allowing for more manageable and efficient pagination of the data.
-
Sampling data − The TOP clause can be used to quickly retrieve a sample of data from a table for testing or analysis.
-
Improving performance − By limiting the number of rows returned, the TOP clause can help improve the performance of a query, especially when dealing with large tables.
-
Debugging − When developing or debugging a query, the TOP clause can be used to quickly return a small number of rows to test the correctness of the query.
-
Data visualization − The TOP clause can be used to limit the number of rows returned for visualization purposes, such as creating charts or graphs.