Documentdb Sql 简明教程

DocumentDB SQL - Between Keyword

BETWEEN 关键字用于针对 SQL 中的如同值范围的查询进行表达。BETWEEN 可用于字符串或数字。在 DocumentDB 和 ANSI SQL 中使用 BETWEEN 的主要区别在于，您可以在混合类型的属性上表达范围查询。

The BETWEEN keyword is used to express queries against ranges of values like in SQL. BETWEEN can be used against strings or numbers. The main difference between using BETWEEN in DocumentDB and ANSI SQL is that you can express range queries against properties of mixed types.

例如，在某些文档中，您可能将“grade”作为数字，而在其他文档中它可能是字符串。在这些情况下，“未定义”两种不同类型的结果之间的比较，并且该文档将被跳过。

For example, in some document it is possible that you might have "grade" as a number and in other documents it might be strings. In these cases, a comparison between two different types of results is "undefined", and the document will be skipped.

我们来考虑先前示例中的三个文档。以下是 AndersenFamily 文档。

Let us consider the three documents from the previous example. Following is the AndersenFamily document.

{
   "id": "AndersenFamily",
   "lastName": "Andersen",

   "parents": [
      { "firstName": "Thomas", "relationship":  "father" },
      { "firstName": "Mary Kay", "relationship":  "mother" }
   ],

   "children": [
      {
         "firstName": "Henriette Thaulow",
         "gender": "female",
         "grade": 5,
         "pets": [ { "givenName": "Fluffy", "type":  "Rabbit" } ]
      }
   ],

   "location": { "state": "WA", "county": "King", "city": "Seattle" },
   "isRegistered": true
}

以下是 SmithFamily 文档。

Following is the SmithFamily document.

{
   "id": "SmithFamily",

   "parents": [
      { "familyName": "Smith", "givenName": "James" },
      { "familyName": "Curtis", "givenName": "Helen" }
   ],

   "children": [
      {
         "givenName": "Michelle",
         "gender": "female",
         "grade": 1
      },

      {
         "givenName": "John",
         "gender": "male",
         "grade": 7,

         "pets": [
            { "givenName": "Tweetie", "type": "Bird" }
         ]
      }
   ],

   "location": {
      "state": "NY",
      "county": "Queens",
      "city": "Forest Hills"
   },

   "isRegistered": true
}

以下是 WakefieldFamily 文档。

Following is the WakefieldFamily document.

{
   "id": "WakefieldFamily",

   "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
   ],

   "children": [
      {
         "familyName": "Merriam",
         "givenName": "Jesse",
         "gender": "female",
         "grade": 6,

         "pets": [
            { "givenName": "Charlie Brown", "type": "Dog" },
            { "givenName": "Tiger", "type": "Cat" },
            { "givenName": "Princess", "type": "Cat" }
         ]
      },

      {
         "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 3,

         "pets": [
            { "givenName": "Jake", "type": "Snake" }
         ]
      }
   ],

   "location": { "state": "NY", "county": "Manhattan", "city": "NY" },
   "isRegistered": false
}

我们来通过一个示例了解一下，其中该查询返回所有第一个孩子的成绩介于 1-5（包括边界）的家庭文档。

Let’s take a look at an example, where the query returns all family documents in which the first child’s grade is between 1-5 (both inclusive).

以下是使用 BETWEEN 关键字并随后使用 AND 逻辑运算符的查询。

Following is the query in which BETWEEN keyword is used and then AND logical operator.

SELECT *
FROM Families.children[0] c
WHERE c.grade BETWEEN 1 AND 5

执行以上查询后，将产生以下输出。

When the above query is executed, it produces the following output.

[
   {
      "givenName": "Michelle",
      "gender": "female",
      "grade": 1
   },

   {
      "firstName": "Henriette Thaulow",
      "gender": "female",
      "grade": 5,

      "pets": [
         {
            "givenName": "Fluffy",
            "type": "Rabbit"
         }
      ]
   }
]

要显示超出先前示例范围的成绩，请如以下查询所示使用 NOT BETWEEN。

To display the grades outside the range of the previous example, use NOT BETWEEN as shown in the following query.

SELECT *
FROM Families.children[0] c
WHERE c.grade NOT BETWEEN 1 AND 5

执行此查询时。它生成以下输出。

When this query is executed. It produces the following output.

[
   {
      "familyName": "Merriam",
      "givenName": "Jesse",
      "gender": "female",
      "grade": 6,

      "pets": [
         {
            "givenName": "Charlie Brown",
            "type": "Dog"
         },

         {
            "givenName": "Tiger",
            "type": "Cat"
         },

         {
            "givenName": "Princess",
            "type": "Cat"
         }
      ]
   }
]