Azure Cosmos DB 中的 ORDER BY 子句ORDER BY clause in Azure Cosmos DB

可选的 ORDER BY 子句指定查询返回的结果的排序顺序。The optional ORDER BY clause specifies the sorting order for results returned by the query.

语法Syntax

ORDER BY <sort_specification>  
<sort_specification> ::= <sort_expression> [, <sort_expression>]  
<sort_expression> ::= {<scalar_expression> [ASC | DESC]} [ ,...n ]  

参数Arguments

  • <sort_specification>

    指定对查询结果集进行排序时要依据的属性或表达式。Specifies a property or expression on which to sort the query result set. 可将排序列指定为名称或属性别名。A sort column can be specified as a name or property alias.

    可以指定多个属性。Multiple properties can be specified. 属性名称必须唯一。Property names must be unique. ORDER BY 子句中排序属性的顺序定义了排序结果集的组织方式。The sequence of the sort properties in the ORDER BY clause defines the organization of the sorted result set. 也就是说,结果集首先按第一个属性排序,然后该有序列表按第二个属性排序,依此类推。That is, the result set is sorted by the first property and then that ordered list is sorted by the second property, and so on.

    ORDER BY 子句中引用的属性名称必须与 select 列表中的某个属性或者与在 FROM 子句中指定的集合中定义的某个属性相对应,且不存在任何多义性。The property names referenced in the ORDER BY clause must correspond to either a property in the select list or to a property defined in the collection specified in the FROM clause without any ambiguities.

  • <sort_expression>

    指定一个或多个属性或表达式用作排序查询结果集的依据。Specifies one or more properties or expressions on which to sort the query result set.

  • <scalar_expression>

    有关详细信息,请参阅标量表达式部分。See the Scalar expressions section for details.

  • ASC | DESC

    指定应当按升序或降序对指定列中的值进行排序。Specifies that the values in the specified column should be sorted in ascending or descending order. ASC 将按照从最低值到最高值的顺序进行排序。ASC sorts from the lowest value to highest value. DESC 将按照从最高值到最低值的顺序进行排序。DESC sorts from highest value to lowest value. ASC 是默认排序顺序。ASC is the default sort order. Null 值被视为最低的可能值。Null values are treated as the lowest possible values.

备注Remarks

ORDER BY 子句要求索引策略包含所要排序的字段的索引。The ORDER BY clause requires that the indexing policy include an index for the fields being sorted. Azure Cosmos DB 查询运行时支持根据属性名称排序,而不支持根据计算的属性排序。The Azure Cosmos DB query runtime supports sorting against a property name and not against computed properties. Azure Cosmos DB 支持多个 ORDER BY 属性。Azure Cosmos DB supports multiple ORDER BY properties. 若要运行包含多个 ORDER BY 属性的查询,应在所要排序的字段中定义组合索引In order to run a query with multiple ORDER BY properties, you should define a composite index on the fields being sorted.

示例Examples

例如,以下查询按居住城市名称的升序检索家庭:For example, here's a query that retrieves families in ascending order of the resident city's name:

    SELECT f.id, f.address.city
    FROM Families f
    ORDER BY f.address.city

其结果是:The results are:

    [
      {
        "id": "WakefieldFamily",
        "city": "NY"
      },
      {
        "id": "AndersenFamily",
        "city": "Seattle"
      }
    ]

以下查询按项的创建日期检索家庭 idThe following query retrieves family ids in order of their item creation date. creationDate 是一个数字,表示纪元时间,或者自 1970 年 1 月 1 日开始消逝的时间(以秒为单位)。 Item creationDate is a number representing the epoch time, or elapsed time since Jan. 1, 1970 in seconds.

    SELECT f.id, f.creationDate
    FROM Families f
    ORDER BY f.creationDate DESC

其结果是:The results are:

    [
      {
        "id": "WakefieldFamily",
        "creationDate": 1431620462
      },
      {
        "id": "AndersenFamily",
        "creationDate": 1431620472
      }
    ]

此外,可按多个属性排序。Additionally, you can order by multiple properties. 按多个属性排序的查询需要组合索引A query that orders by multiple properties requires a composite index. 请考虑下列查询:Consider the following query:

    SELECT f.id, f.creationDate
    FROM Families f
    ORDER BY f.address.city ASC, f.creationDate DESC

此查询按城市名称的升序检索家庭 idThis query retrieves the family id in ascending order of the city name. 如果多个项包含同一个城市名称,该查询将按 creationDate 的降序排序。If multiple items have the same city name, the query will order by the creationDate in descending order.

后续步骤Next steps