sql limit 子句_SQL Order by子句概述

sql limit 子句

In this article, we’ll walk-through the concept of the SQL Order by clause and understand how the SQL engine works with the ordering result in a query.

在本文中，我们将逐步了解SQL Order by子句的概念，并了解SQL引擎如何与查询中的排序结果一起使用。

介绍 (Introduction)

SQL has been anointed the world’s third most powerful Data Science, Machine Learning software programming language. Most real-time systems use SQL to manage the incredible amount of data they work with on a daily basis. As a result, understanding how a query works in Microsoft SQL Server is a highly valuable skill.

SQL已被誉为世界第三强大的数据科学，机器学习软件编程语言。 大多数实时系统都使用SQL来管理每天处理的大量数据。 因此，了解查询在Microsoft SQL Server中的工作方式是一项非常有价值的技能。

开始吧 (Get started)

Let us dive into the SQL Order by clause but first, let’s attempt to understand why data is not ordered in a table by default. To answer this question, let us take a step back and review the concepts of set theory. In mathematical terms, SQL Server uses the table to represent the concept of a Relation. Every relation contains a set of elements that define the properties of a relation and is commonly known as Set. We also learn that a relation is considered to be a set or subset and follow no order. As a result, a SQL Server table guarantees that it has no defined order for the rows found within it.

让我们深入研究SQL Order by子句，但首先，让我们尝试了解为什么默认情况下表中的数据未排序。 为了回答这个问题，让我们退后一步，回顾一下集合论的概念。 用数学术语来说，SQL Server使用该表来表示关系的概念。 每个关系都包含一组定义关系属性的元素，通常称为Set。 我们还了解到，关系被视为集合或子集，并且不遵循任何顺序。 结果，SQL Server表保证它在其中找到的行没有定义的顺序。

指导方针 (Guidelines)

1. A SQL Server result-set is a factor of the way in which the data was entered into the table. In some cases, the data appeared to be in sorted order but this doesn’t mean that data entered and data stored in the hard-drive are same
   SQL Server结果集是将数据输入表的方式的一个因素。 在某些情况下，数据似乎是按顺序排列的，但这并不意味着输入的数据和存储在硬盘驱动器中的数据是相同的
2. SQL Server doesn’t guarantee the order of the result-set. It’s independent of the order unless we define it explicitly on how we want those records sorted. The only way to change the order in which the results appear is to use the SQL Order by clause
   SQL Server不保证结果集的顺序。 除非我们明确定义要如何对这些记录进行排序，否则它与顺序无关。 更改结果显示顺序的唯一方法是使用SQL Order by子句
3. The SQL Order by clause is not valid for in-line functions, views, derived tables, and sub-queries, unless it is specified with SQL TOP or OFFSET and FETCH clauses
   除非使用SQL TOP或OFFSET和FETCH子句指定，否则SQL Order by子句对嵌入式函数，视图，派生表和子查询无效。
4. Having constraints, clustered index, identity values or sequences doesn’t guarantee the ordering of the results. Again, if you get an output in the sorted order then it is just a coincidence of the fact that data entered in the table in the same order of the sorting of the data.
   具有约束，聚簇索引，标识值或序列不能保证结果的顺序。 同样，如果您获得按排序顺序的输出，则这只是事实，即数据以与数据排序相同的顺序输入表中的事实是巧合的。
5. A SQL query that uses set operators such as UNION, EXCEPT, or INTERSECT, SQL Order by is allowed only at the end of the statement
   仅在语句末尾允许使用使用UNION，EXCEPT或INTERSECT等集合运算符SQL查询，SQL Order by

例子 (Examples)

In this section, we’re going to see a few examples of SQL, so that we can have a better understanding of how we’re actually working with SQL Order by clause. So let’s get started with few samples.

在本节中，我们将看到一些SQL示例，以便我们可以更好地了解如何实际使用SQL Order by子句。 因此，让我们从几个样本开始。

如何在SQL查询中使用SQL Order by子句 (How to use the SQL Order by clause in a SQL query)

In the following example we’re going to run the sample SQL without using the SQL Order by clause. The output is a clear indication that when we run a query in SQL Server, the query optimizer looks at the data request and generates a query plan and returns the records from the table or tables, and it’s based on the query and also it is based on how the data is physically organized on the drive.

在下面的示例中，我们将在不使用SQL Order by子句的情况下运行示例SQL。 输出清楚地表明，当我们在SQL Server中运行查询时，查询优化器将查看数据请求并生成查询计划，并从一个或多个表中返回记录，它基于查询，也基于有关如何在驱动器上物理组织数据的信息。

SELECT BusinessEntityID, 
       FirstName, 
       MiddleName, 
       LastName   
FROM Person.Person;
GO

Note: The SQL Order by clause only provides sorting of the records in the results set. The Order by clause does not affect the ordering of the records in the source table or changing the physical structure of the table. It is just a logical re-structuring of physical data.

注意：SQL Order by子句仅对结果集中的记录进行排序。 Order by子句不会影响源表中记录的顺序或更改表的物理结构。 这只是物理数据的逻辑重组。

Next, add the SQL Order By clause in the query. You can see an ordered result-set sorted with ascending order based on the value BusinessEntityID.

接下来，在查询中添加SQL Order By子句。 您可以看到基于值BusinessEntityID以升序排序的有序结果集。

SELECT BusinessEntityID, 
       FirstName, 
       MiddleName, 
       LastName
FROM Person.Person
ORDER BY BusinessEntityID;
GO

In this case, SQL Server uses BusinessEntityID and returns all of the records and it will order the result set based on that specific field.

在这种情况下，SQL Server使用BusinessEntityID并返回所有记录，它将根据该特定字段对结果集进行排序。

Note: The default ORDER is ascending order and result-set is sorted in ascending order based on the field that is specified in the SQL query.

注意：默认的ORDER为升序，结果集基于SQL查询中指定的字段以升序排序。

如何使用数字并指定降序 (How to use Numbers and specify descending order )

In the following example we’re going to change the order of the records. In this case, we’re going to use the LastName as the Order by column and explicitly mention the keyword DESCENDING or DESC to specify the sorting order. You also may notice that the number 4 is specified in the order by clause. The number 4 specifies the position of the columns in the SQL query. In this case, position of BusinessEntityID is 1, FirstName is 2, MiddleName is 3 and LastName is 4.

在下面的示例中，我们将更改记录的顺序。 在这种情况下，我们将使用LastName作为Order by列，并显式提及关键字DESCENDING或DESC以指定排序顺序。 您可能还会注意到，数字4是在order by子句中指定的。 数字4指定SQL查询中列的位置。 在这种情况下，BusinessEntityID的位置为1，名字为2，中间名称为3，姓氏为4。

SELECT BusinessEntityID, 
       FirstName, 
       MiddleName, 
       LastName
FROM Person.Person
ORDER BY 4 DESC;
GO

Note: A sort column can be specified as a name or column alias, or a non-negative integer representing the position of the name or alias in the select list.

注意：可以将排序列指定为名称或列别名，也可以将非负整数指定为名称或别名在选择列表中的位置。

如何在同一查询中指定升序和降序 (How to specify ascending and descending order in the same query)

The following example orders the result set on FirstName ascending order and LastName Descending order. The query result set is first sorted based on the ascending order by the FirstName column and then sorted in descending order by the LastName column.

下面的示例对结果集按FirstName升序和LastName降序排序。 查询结果集首先按FirstName列的升序排序，然后再按LastName列的降序排序。

SELECT FirstName, 
       MiddleName, 
       LastName
FROM Person.Person
ORDER BY FirstName ASC, 
         LastName DESC;
GO

Now, we can see that the data is sorted by the FirstName ascending order and then you’ll see sorted results with a sorting of the LastName descending order. So let’s take a look at the output. In this case, FirstNames is “Aaron” and then we have LastName sorted descending order and you’ll notice LastName field is alphabetical order starting from Zhang, Young, Yang etc.

现在，我们可以看到数据是按照名字的升序排序的，然后您将看到按姓氏降序排序的排序结果。 因此，让我们看一下输出。 在这种情况下，FirstNames是“ Aaron”，然后我们将LastName排序为降序，您会注意到LastName字段是按字母顺序从Zhang，Young，Yang等开始的。

Now, you can get an idea of how that ORDER BY using two different fields, or two different field names changes the way the result set comes in. Priority is given to sorting on the FirstName, and then within the results of those FirstNames, we will sort the remaining values by the LastName.

现在，您可以了解如何使用两个不同的字段或两个不同的字段名称来更改ORDER BY，从而改变结果集的输入方式。优先考虑对FirstName的排序，然后在这些FirstName的结果中，将按姓氏对其余值进行排序。

如何指定条件顺序 (How to specify the conditional order)

The following examples use the CASE expression in an Order by clause to conditionally determine the sort order of the rows based on a given column value.

以下示例在Order by子句中使用CASE表达式来基于给定的列值有条件地确定行的排序顺序。

In this case, the value of the gender column is evaluated. If the value of the column is M then the corresponding BusinessEntityID is sorted in descending order. If the value found is “F” then the BusinessEntityID column is sorted in an ascending order.

在这种情况下，将评估“性别”列的值。 如果该列的值为M，则对应的BusinessEntityID将按降序排序。 如果找到的值为“ F”，则BusinessEntityID列将按升序排序。

WITH cte
     AS (SELECT e.Gender Gender, 
                e.VacationHours VacationHours, 
                p.Rate Rate, 
                e.BusinessEntityID BusinessEntityID
         FROM HumanResources.Employee e
              INNER JOIN HumanResources.EmployeePayHistory p ON e.BusinessEntityID = p.BusinessEntityID)
     SELECT *
     FROM cte
     WHERE Rate > 50
     ORDER BY CASE GENDER
                  WHEN 'M'
                  THEN BusinessEntityID
              END DESC,
              CASE GENDER
                  WHEN 'F'
                  THEN BusinessEntityID
              END;

The output shows all the employee details whose rate is over 50 and BusinessEntityID is sorted descending order for male employees and sorted Ascending order for female employees.

输出显示速率超过50的所有雇员详细信息，BusinessEntityID对男性雇员按降序排序，对女性雇员按升序排序。

如何与UNION，EXCEPT和INTERSECT运算符一起使用ORDER BY (How to use ORDER BY with UNION, EXCEPT, and INTERSECT operators)

The following example the query uses the UNION ALL operator. You can see that the SQL Order by clause must be specified at the end of the SQL statement and the results of the combined queries are sorted.

下面的示例查询使用UNION ALL运算符。 您可以看到，必须在SQL语句的末尾指定SQL Order by子句，并对合并查询的结果进行排序。

• SQL Union for more information.SQL Union了解更多有关此主题的信息。

SELECT TOP  5 FirstName, 
       MiddleName, 
       LastName
FROM Person.Person
WHERE FirstName LIKE 'A%'
UNION
SELECT TOP 5 FirstName, 
       MiddleName, 
       LastName
FROM Person.Person
WHERE FirstName LIKE 'B%'
ORDER BY FirstName, 
         LastName DESC;

摘要 (Summary)

So far we discussed several examples to understand the concepts of SQL Order by clause. We learned that SQL Server doesn’t guarantee any order of the results stored in the table, nor in the results set returned from your queries, but we can sort the output by using the order by clause.

到目前为止，我们讨论了一些示例来理解SQL Order by子句的概念。 我们了解到，SQL Server不保证表中存储的结果或查询返回的结果集中的任何顺序，但是我们可以使用order by子句对输出进行排序。

That’s all for now… Thanks for reading this post. Please leave any questions or feedback in the comments below.

现在就这些了……感谢您阅读这篇文章。 请在下面的评论中留下任何问题或反馈。

翻译自: https://www.sqlshack.com/overview-of-the-sql-order-by-clause/

sql limit 子句