查询 SQL Server 2012：第一部分

1. 目录

第一部分

1. 目录
2. 引言
3. 安装示例数据库
4. 定义查询
5. 我们的第一个查询；SELECT 语句
• 5.1. 别名
6. 消除重复项；DISTINCT 
7. 过滤数据；WHERE 子句
   • 7.1. NOT 关键字
   • 7.2. 组合谓词
   • 7.3. 过滤字符串
   • 7.4. 过滤日期
   • 7.5. IN 和 BETWEEN 关键字
8. 排序数据；ORDER BY
9. 进一步限制结果；TOP 和 OFFSET-FETCH
   • 9.1. TOP
   • 9.2. OFFSET-FETCH
10. 聚合数据；GROUP BY 和 HAVING
11. 从多个表中选择；使用 JOIN
    • 11.1. CROSS JOIN
    • 11.2. INNER JOIN
    • 11.3. OUTER JOIN
    • 11.4. 自连接
    • 11.5. 多个 JOIN
12. 多个分组；GROUPING SETS
    • 12.1. GROUPING SETS
    • 12.2. CUBE
    • 12.3. ROLLUP
    • 12.4. GROUPING
    • 12.5. GROUPING_ID
13. 窗口函数；OVER 子句
    • 13.1. 聚合函数
    • 13.2. 框架
    • 13.3. 排名函数
    • 13.4. 偏移函数
14. 还没完……

第二部分 

1. 目录
2. 欢迎回来！
3. 查询中的查询；子查询
   • 3.1. 用不同的方式编写相同的查询
   • 3.2. 更多过滤选项；IN、ANY、SOME、ALL 和 EXISTS
   • 3.2.1. IN
   • 3.2.2. ANY 和 SOME
   • 3.2.3. ALL
   • 3.2.4. EXISTS
4. 从子查询中查询；派生表
5. 公用表表达式，又名 CTE
6. 集合运算符；UNION、INTERSECT 和 EXCEPT
   • 6.1. 合并集合；UNION 和 UNION ALL
   • 6.2. 使用 CTE 和 UNION ALL 进行递归
   • 6.3. INTERSECT
   • 6.4. EXCEPT
7. 在表上推移；PIVOT 和 UNPIVOT
   • 7.1. 数据透视
   • 7.2. 数据逆透视
8. 表表达式的更多用法；APPLY
   • 8.1. CROSS APPLY
   • 8.2. OUTER APPLY
9. 查询的其他方面
   • 9.1. 类型转换；CAST 和 CONVERT、PARSE 和 FORMAT
   • 9.1.1 CAST 和 CONVERT
   • 9.1.2 PARSE
   • 9.1.3 FORMAT
   • 9.2. VARCHAR 函数
   • 9.3. DATETIME 函数
   • 9.4. CASE 和 IIF
   • 9.4.1 CASE
   • 9.4.2 IIF
   • 9.5. COALESCE、ISNULL 和 NULLIF
10. 结论

2. 引言

我目前正在为我的“查询 Microsoft SQL Server 2012”考试（70-461）（更新：我已通过考试，耶！:-)）而学习。我已有几年 SQL Server（2005 和 2008）的使用经验，但我从未真正深入过。因此，我能够进行查询表、应用 where 子句、分组数据等操作，但从未真正精通。每当事情变得复杂一些时，我都需要向 Google 或同事寻求帮助。然而，我发现我每天都需要越来越多地使用 SQL Server，因此我决定至少阅读一本关于它的书。我选择的书是《查询 Microsoft SQL Server 2012》。

因此，在撰写本文时，我正在为我的考试刻苦学习。还有什么比通过写作来学习更好的方法呢？所以我决定这样做。写关于它。我将要写的所有内容都在我正在阅读的书（实际是第 1 到第 5 章）中有讨论，可能还有很多其他书籍和互联网、CodeProject 以及无数其他网站也在讨论。 

那么，为什么您仍然应该阅读本文而不是我刚才提到的书或那些其他文章呢？首先，我的文章是免费的，而大多数书都不是。其次，本文的阅读速度比大多数书籍快。第三，本文以结构化的方式讨论了许多查询子句（这是许多网站所缺乏的）。接下来，也许一个仍在学习的人的观点和写作方式可能适合那些仍在学习自己的人。最后，本文提供了许多您可以复制/粘贴到 SQL Server 查询窗口中进行尝试的示例。最后但并非最不重要的一点是，我还加入了一些屏幕截图！

那么，本文是关于什么的呢？它是关于从数据库中选择数据。这听起来可能很枯燥，但实际上在一个 SELECT 语句中，您可以做很多事情。实际上，我决定将本文分为两部分。
您现在正在阅读的第一部分侧重于使用单个 SELECT 语句从数据库中选择数据。我们将进一步深入了解分组和窗口函数。在本部分文章结束时，您应该能够创建查询，从一个或多个表中选择、过滤、分组和排序数据。
第二部分是关于在使用多个 SELECT 语句的同时仍然返回单个结果集。
您可以在 CodeProject 上阅读第二篇文章：查询 SQL Server 2012：第二部分。 

在本文中，我假设您具备一些非常基本的数据库知识。了解如何安装数据库、打开查询窗口、运行查询（F5）以及浏览表会很有帮助。本文不涵盖表的创建、索引和其他对象，也不涵盖插入、修改和删除数据。 

3. 安装示例数据库

如前所述，我正在学习 SQL Server 2012 认证。所以当然你需要 SQL Server 2012。我下载了免费的 SQL Server 2012 Express Edition，这也是我写本文时使用的。不幸的是，我无法帮助您安装 SQL Server。但是，很可能您已经安装了。如果您没有，可以点击以下链接找到它：下载 SQL Server 2012 Express。点击写着“下载 SQL Server 2012 Express”的大红按钮，然后选择您想要的版本。我不确定它们之间的区别（毕竟我才刚开始学），但我选择了“带有高级服务的 SQL Server Express”，因为它看起来拥有一个人可能需要的一切。所以下载它，安装它，然后完成后继续阅读……请便，我等着您。

好了吗？接下来您需要做的是安装一个示例数据库供您使用。最好安装我正在使用的同一个数据库，即 AdventureWorks2012 示例数据库。

获取这个数据库比安装 SQL Server 2012 还要困难。您可以在 SQL Server 示例数据库 CodePlex 网站上获取。在撰写本文时，您有 14(!) 种下载可供选择。这是怎么回事！？所以我挑选了几个，并成功下载了 AdventureWorks2012-Full Database Backup.zip（在下载列表的底部附近）。我本来想将其包含在本文中，但 CP 提示我将文件保持在 10 MB 以下。

所以下载 zip 文件，然后解压缩里面的 .bak 文件。幸运的是，我可以帮助您将它导入 SQL Server。将 .bak 文件放在您的 SQL Server 备份文件夹中。如果您在安装过程中没有更改任何设置，它应该在 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012EXPRESS\MSSQL\Backup。现在，进入您的 SQL Server 2012 实例，打开对象资源管理器（如果尚未打开），然后右键单击“数据库”文件夹。在上下文菜单中，选择“还原数据库”。

您现在应该会看到“还原数据库”窗口。

 

点击“设备”，然后点击其后面的按钮。您现在应该会看到“选择备份设备”窗口。点击“添加”，然后浏览到您刚刚解压缩的 .bak 文件（如果未立即看到）。选择 .bak 文件，然后点击“确定”。确保在“选择备份设备”窗口中没有选择其他文件。点击“确定”，您应该会返回到“还原数据库”窗口。您可以查看您的选项，然后再次点击“确定”。AdventureWorks 数据库现在应该已还原并可供使用。

如果您有更好的方法来设置和运行数据库，请在评论部分告知我。

4. 定义查询

现在 AdventureWorks 已启动并运行，我们就可以开始编写我们的第一个查询了。在这样做之前，我想让您花点时间思考一下查询是什么。认真思考一下。维基百科对此的解释是：“总的来说，查询是一种提问形式，是一种探究的方式”。好的，那么我们将要质询数据库。 

那么，我们究竟将如何质询数据库呢？当您让朋友给您拿点东西，比如饮料时，您可能会问朋友：“能给我拿杯饮料吗？”。如果您想礼貌一些，可能会在句子中加入“请”字，但这并不会真正改变您的问题或查询的含义。当然，数据库无法理解这样的问题。数据库只能“理解”预定义的一组语句，并且需要按特定顺序执行，它是结构化的。现在猜猜看，SQL 的意思就是结构化查询语言！ 这种语言实际上是我将在本文中向您解释的关于数据库的部分。
我应该提到，SQL Server 并不完全使用 ANSI 和 ISO SQL 标准语法。它使用 T-SQL（Transact-SQL），这是 SQL 标准的一种方言。这基本上意味着本文中的查询也可能在 Oracle、MySQL、PostgreSQL 和 Firebird 等其他 SQL 数据库中工作，但不能保证（也因为所有这些其他数据库都有自己的方言）。本文不是关于 SQL 方言差异的，但我确实想在此提及。

在开始之前，我们需要知道我们要查询什么。正如您可能知道的，SQL Server 是一个关系数据库。之所以这样称呼，是因为它以关系（而不是因为一块数据与其他一块数据之间存在关系，很多人这样认为）的形式存储数据。关系是一个数学术语，表示一组唯一且无序的元组，或值集（或属性）。在 SQL Server 中，它们表示为表，是行和列的集合。我们将要查询的正是这些表。下图显示了我们刚刚安装的数据库中的 Production.Product 表的一部分。

让我们更实际地看一看。假设我们要将我们的 CD 收藏存储在数据库中。一个表可以是一组 CD 对象。为了简单起见，假设一张 CD 有艺术家、标题和发行日期。所以我们的表现在是包含两个名称（艺术家姓名和标题）和一个日期（发行日期）的元组的集合。我们现在可以查询数据库，查找 John Williams 的所有 CD，或者在 2012 年发行的 CD。或者，假设一张 CD 还有“歌曲数量”属性。我们可能想知道 John Williams 所有 CD 的歌曲总数。或者，也许一张 CD 的平均歌曲数量（按艺术家或总计）。

如何创建、填充和维护表不在本文的范围之内。读者应至少了解什么是表以及它看起来是什么样的。

5. 我们的第一个查询；SELECT 语句

我听到您在想“什么时候才能终于看到一些 SQL 代码呢！”。现在。

SELECT 'Hello, query!'

还记得您让朋友拿饮料吗？您本可以说“拿 饮料”。他可能不会以那种语气（命令式而非询问式）去拿。幸运的是，SQL Server 会满足您的所有要求。只是我们需要将这个例子中的 GET 替换为 SELECT。SELECT 关键字是您在大多数查询中使用到的第一个词。实际上，任何查询至少都包含 SELECT 语句。在上面的查询中，数据库简单地返回一个只有一行一列的表，其值为“Hello query”（不包括撇号）。

在选择数据时，我们需要用逗号分隔我们要选择的“事物”。下面的示例生成一行两列。在 SELECT 列表中，两个值用逗号分隔。

SELECT 'Hello, query!', 'A second value' 

当然，上面的查询并没有多大用处。我们想查询数据库中实际存储的数据。为了获取这些数据，我们首先需要指明数据库应该从哪里获取数据。这是通过使用 FROM 语句完成的。假设我们想从 AdventureWorks2012 数据库中选择一些关于产品的数据。

SELECT ProductID, Name, ProductNumber FROM Production.Product 

实际上，这个查询很有意义，不是吗？ProductID、Name 和 ProductNumber 是 Production.Product 表的列（Production 实际上是 Product 表的命名空间，在数据库中，命名空间和表名必须唯一）。再次注意，列之间用逗号分隔。查询结果是一个有三列的表，行数等于 Production.Product 表中的产品数。

选择表中所有列的快捷方式是以下语法。

SELECT * FROM Production.Product

这将返回 Production.Product 表中的所有内容。但强烈建议不要使用此语法。这主要与底层表定义有关，并且超出了本文的范围。不过，我写了一篇关于使用 SELECT * 的真实经历的文章，如果您感兴趣，可以阅读：《“Select *”的危害》。尽管如此，我仍然会在本文中大量使用这种语法，只是不要在生产代码中使用！

要选择的列列表可以按任何顺序出现，不一定非得按它们在列中出现的顺序。下面的查询显示了一个示例。

SELECT Color, ProductNumber, Name
FROM Production.Product 

所以，让我们看一下通用的 SELECT... FROM 语法。一般来说，您可以说它看起来像这样

SELECT <List of columns> FROM <Table name> 

5.1 别名

即使是最简单的查询，您还可以做一些其他事情。其中之一就是别名。我们实际上回到了第一个查询，SELECT 'Hello, query!'。看一下输出，您注意到什么了吗？

此查询返回的那个列没有名称！它当然没有名称。当我们从表中选择列时，SQL Server 默认会继承结果中选中列的名称。但是，此示例中我们没有从表中选择，结果 SQL Server 不知道如何命名该列。看看您选择附加值时会发生什么。您现在有两列没有名称。幸运的是，我们不必像“不可提及者”那样谈论列。我们可以实际为列分配别名。有几种方法可以做到这一点，我将向您展示一种（因为这是推荐的语法）。

AS <alias> 语法可以为您的结果列赋予新名称。请注意，AS 关键字是可选的。

当从表中选择列时，也允许使用别名。

SELECT
    ProductID        AS ID,
    Name            AS ProductName,
    ProductNumber        AS ProductNumber,
    Color
FROM Production.Product

如您所见，可以为结果列（如 ID 和 ProductName）赋予新名称，也可以使用与列同名的别名（ProductNumber 保持不变），或者根本不使用别名（Color 未使用别名）。当您将要操作数据或使用函数时（我们将在本文后面看到），列别名将是必需的。
列别名不能在您查询的其他部分使用，例如 WHERE、HAVING 和 ON 子句（这些将在本文后面讨论）。此规则的唯一例外是 ORDER BY 列表（稍后也将讨论）。

也可以为表设置别名。当从多个表中选择时，这尤其有用。我稍后会回到这一点。

因此，现在查询表的通用语法如下所示

SELECT
    <Column name> (AS Alias)
    ...
FROM <Table name> (AS Alias) 

我们现在已经了解了 SQL Server 2012 查询的基础知识。在下一章中，我们将研究如何限制我们的结果。

6. 消除重复项；DISTINCT

有时查询可能会返回重复的行。许多产品具有相同的颜色。如果我们只选择产品表中的颜色，我们会得到每个单独产品的颜色。这看起来是这样的

这肯定不是我们想要的。这也不符合关系原则。关系是唯一且无序的值的集合，还记得吗？幸运的是，使用 DISTINCT 关键字可以相对容易地删除重复结果。DISTINCT 关键字放在 SELECT 之后。下面的查询演示了这一点。

所有产品总共有九种颜色（NULL 不是一种颜色，这一点将在下一节中讨论）。 

7. 过滤数据；WHERE 子句

当然，您会想编写更高级的查询。您可能想选择所有特定颜色的产品，价格超过 1000 美元的产品，或者根本没有价格的产品。这称为过滤数据。

SQL Server 中的过滤可以通过三种方式完成，即使用 ON、WHERE 和 HAVING 子句。本章将讨论 WHERE 子句。ON 和 HAVING 子句将在本文后面讨论。

SQL 中的过滤基于谓词。谓词是另一个数学术语，表示返回TRUE或FALSE（也称为Boolean）的函数。例如，想象一个检查某人是否年满 18 岁的函数。这样的函数会返回真或假，一个人要么大于 18 岁，要么不是。

让我们看一个简单的 SQL 示例。我们想要一个价格超过 1000 美元的所有产品的列表。我们可以使用 WHERE 子句来完成此操作。

SELECT *
FROM Production.Product
WHERE ListPrice > 1000 

运行此查询会返回 Production.Products 表中 504 个产品中的 86 个。注意您可以使用以下符号进行比较

>    = greater than
<    = less than
>=    = greater than or equal to
<=    = less than or equal to
<>    = not equal to (equal to != in C-based languages)
=    = equal to (equal to == in C-based languages) 

您可能已经习惯了这种语法。SQL 也支持它（以及其他）。在此示例中，ListPrice > 1000 是谓词。当使用 WHERE 子句时，SQL 会丢弃所有谓词返回FALSE的结果（在这种情况下，当产品的 ListPrice 不大于 1000 时，谓词返回FALSE）。

但是有一个陷阱。 考虑以下查询

SELECT *
FROM Production.Product
WHERE Weight >= 0

Production.Product 表中有 504 个产品，没有产品的重量是负数。但此查询仅返回 205 个结果。399 个产品去哪儿了！？如果您查看产品表，可能会注意到许多单元格的值为 NULL。这里事情就变得棘手了。NULL 实际上不是一个值。它表示该元组的属性（或该行的列）没有值。那么，在上面的查询中，我们如何将 NULL（或无值）与 0 进行比较呢？我们可以认为在这种情况下 NULL 等于 0，但 SQL Server simply 将 NULL 视为与其他任何值都不相等。NULL 甚至不等于 NULL。 尽管以下语法未在本文中涵盖，但它应该非常简单明了。

幸运的是，并非一切都希望渺茫，我们可以使用 IS 关键字来比较 NULL 值。

所以现在我必须纠正自己。之前我说谓词是返回TRUE或FALSE的函数。当处理 NULL 时，它可以返回一个附加值，即UNKNOWN。这被称为三值逻辑。 SQL 会丢弃在 WHERE 子句中返回FALSE或UNKNOWN的谓词的结果。

以下查询返回 Weight 没有值的（有些人可能会说“值是 NULL”，尽管这不完全正确）所有产品。

SELECT *
FROM Production.Product
WHERE Weight IS NULL

可以按不在 SELECT 列表中的列过滤数据。考虑以下查询，它完全有效，并且也返回正确的结果（这就是我们期望它返回的结果）。

SELECT ProductNumber, Name
FROM Production.Product
WHERE ListPrice > 1000

7.1 NOT 关键字

要否定一个谓词，可以使用 NOT 关键字。例如，假设您想要不是红色的产品。现在您有两个选择，使用 <> 进行比较，或使用 = 结合 NOT 关键字。

SELECT *
FROM Production.Product
WHERE Color <> 'Red' 

或者

SELECT *
FROM Production.Product
WHERE NOT Color = 'Red'

这两个查询返回的结果完全相同。

您也可以在 IS NULL 语句中使用 NOT，即 IS NOT NULL。因此，以下查询返回相同的结果

SELECT *
FROM Production.Product
WHERE Weight IS NOT NULL

返回与以下相同的结果

SELECT *
FROM Production.Product
WHERE NOT Weight IS NULL

以下查询显示了完全有效的语法，但您应该注意此类查询。它返回 Weight 没有值的所有行，但双重否定使其变得令人困惑。

SELECT *
FROM Production.Product
WHERE NOT Weight IS NOT NULL 

7.2 组合谓词

我们可以组合谓词来创建更高级的过滤器。我们可以使用 AND 或 OR 关键字来完成此操作。以下查询返回 ListPrice 大于 1000 的所有产品，但前提是它们也具有红色。

SELECT *
FROM Production.Product
WHERE ListPrice > 1000
    AND Color = 'Red'

尝试一下，看看结果。我们知道有 86 种产品价格超过 1000 美元。 此查询返回 20 行，因此有 20 种产品价格超过 1000 美元且颜色为红色。

除了使用 AND 关键字，我们还可以通过使用 OR 来链接谓词。以下查询返回价格超过 1000 美元或颜色为红色的所有产品。这意味着价格为 1000 美元或更低的产品也将在结果中返回，但前提是它们是红色的。

SELECT *
FROM Production.Product
WHERE ListPrice > 1000
    OR Color = 'Red'

现在看下面的查询。它会返回价格超过 1000 美元的黑色和红色产品，还是会返回价格超过 1000 美元的红色产品以及任何价格的黑色产品？

SELECT *
FROM Production.Product
WHERE ListPrice > 1000
    AND Color = 'Red'
    OR Color = 'Black' 

当您运行此查询时，您可以看到它是第二种情况，查询返回价格超过 1000 美元的红色产品以及任何价格的黑色产品。AND 关键字优先于 OR 关键字。因为这并不总是清晰的，所以最好使用括号，即使查询已经按您想要的方式工作。 括号的优先级始终最高。这意味着括号内的内容会先进行评估。
所以，假设我们不想要价格超过 1000 美元的红色产品和所有黑色产品。相反，我们想要价格超过 1000 美元，并且是红色或黑色的所有产品。使用括号可以轻松完成此任务。

SELECT *
FROM Production.Product
WHERE ListPrice > 1000
    AND (Color = 'Red'
    OR Color = 'Black')

运行此查询，并检查现在所有产品都价格超过 1000 美元。

最后要提的一点是，NOT 优先于 AND 和 OR。所以，在下面的示例中，结果将只包含价格不超过 1000 美元的红色产品以及所有黑色产品。

SELECT *
FROM Production.Product
WHERE NOT ListPrice > 1000
    AND Color = 'Red'
    OR Color = 'Black' 

现在，假设您想要除了价格超过 1000 美元的红色和黑色产品之外的所有产品。您可以使用嵌套括号和 NOT 关键字来轻松完成此任务。

SELECT * 
FROM Production.Product
WHERE NOT (ListPrice > 1000
    AND (Color = 'Red'
    OR Color = 'Black')) 

7.3 过滤字符串

在前面的示例中，我们过滤了数值和字符串（文本）。这很容易。数值不需要任何特殊处理，字符串值应放在单引号内。

那么，我们将如何过滤字符串的一部分呢？例如，我们想要所有以字母“B”开头的产品。这可以使用 LIKE 运算符完成。使用 LIKE 运算符时，可以使用 % 符号表示“零个、一个或多个字符”。考虑以下返回以“B”开头的所有产品的查询。

SELECT *
FROM Production.Product
WHERE Name LIKE 'B%'

所以，名称应该以 B 开头，然后是“零个、一个或多个字符”。那么，如何查询所有以“B”结尾的产品呢？我们想要零个、一个或多个任意字符，然后是一个“B”。

SELECT *
FROM Production.Product
WHERE Name LIKE '%B'

请注意，此查询实际上返回所有以“b”或“B”结尾的产品。SQL 默认不区分大小写，尽管您可以使其区分大小写。本文未涵盖这一点。

当然，我们可以使用 LIKE 来查找字符串中的特定单词或字符。以下查询返回所有是锁（至少根据其名称）的产品。

SELECT *
FROM Production.Product
WHERE Name LIKE '%Lock%' 

以下查询返回所有用于公路自行车的黑色装备。

SELECT *
FROM Production.Product
WHERE Name LIKE '%Road%Black%'

% 符号称为通配符。以下通配符可用于 LIKE 运算符

%                = none, one or more characters
_                = a single character
[<character list>]        = a single character from the list
[<character range>]        = a single character from the range
[^<character list or range>]    = a single character that is not in the list or range

这里有一些所有通配符的示例。请注意，第一个和第二个示例返回相同的结果。

-- First character is anything
-- Second character is an L
-- Third character is a space
SELECT *
FROM Production.Product
WHERE Name LIKE '_L %'
 
-- First character is an M, H or L
-- Second character is an L
SELECT *
FROM Production.Product
WHERE Name LIKE '[MHL]L%'
 
-- First character is an A, B or C
SELECT *
FROM Production.Product
WHERE Name LIKE '[A-C]%'
 
-- First character is not an A, B or C
SELECT *
FROM Production.Product
WHERE Name LIKE '[^A-C]%'

7.4 过滤日期

另一个感兴趣的点是过滤日期。SQL Server 支持多种日期类型，如 DateTime、SmallDateTime 和 DateTime2。它们之间的区别超出了本文的范围。日期有几种过滤选项。日期表示为特定格式的字符串。我来自荷兰，我们这里会这样写日期：31-12-2013（即 2013 年 12 月 31 日）。其他国家/地区有自己的格式。在美国，同一日期会写成 12/31/2013。这很麻烦，因为您不希望查询在不同国家/地区返回不同的结果。因此，建议您始终使用以下格式：20131231，即年用四位数字，月用两位数字，日用两位数字（也称为 yyyyMMdd）。一月一日会写成 20130101。此格式不特定于任何国家/地区，或与文化无关。所以让我们试试。

SELECT *
FROM Production.Product
WHERE ModifiedDate = '20080311' 

运行此查询没有返回任何结果。出了什么问题！？日期还有一个“问题”，那就是它们通常带有时间（而不是 00:00:00.000）。以下查询会返回结果（显然所有产品都在同一毫秒内被修改）。

SELECT *
FROM Production.Product
WHERE ModifiedDate = '20080311 10:01:36.827'

当然，这种语法很繁琐，并且按天或日期范围选择所有数据将变得不可能。因此，您应该搜索日期大于或等于您要搜索的日期且小于该日期之后一天的记录。在此示例中，我们将使用以下查询。

SELECT *
FROM Production.Product
WHERE ModifiedDate >= '20080311'
    AND ModifiedDate < '20080312' 

关于过滤日期的最后一点提醒。2013-12-31（或 yyyy-MM-dd）格式是 ISO 标准，在 DateTime 和 SmallDateTime 数据类型上可能似乎工作良好，但出于历史原因，此格式不与文化无关。

还有其他过滤日期的方法，例如使用函数，但它们将在本文的第二部分讨论。

7.5 IN 和 BETWEEN 关键字

您还可以使用 BETWEEN 和 IN 关键字来过滤数据。两者都允许您过滤值范围。区别在于 BETWEEN 使用连续值，而 IN 允许您使用任何值集。

以下查询返回价格在 100 美元到 200 美元之间（包括 100 美元和 200 美元）的所有产品。

SELECT *
FROM Production.Product
WHERE ListPrice BETWEEN 100 AND 200

该查询等同于以下查询

SELECT *
FROM Production.Product
WHERE ListPrice >= 100
    AND ListPrice <= 200

IN 运算符允许您指定要查找的值，用逗号分隔。假设我们想要产品模型为 15、25 或 30 的所有产品。以下查询将返回正确的结果。

SELECT *
FROM Production.Product
WHERE ProductModelID IN (15, 25, 30) 

该查询等同于以下查询

SELECT *
FROM Production.Product
WHERE ProductModelID = 15
    OR ProductModelID = 25
    OR ProductModelID = 30 

8. 排序数据；ORDER BY

很多时候您会想对数据进行排序，这意味着以特定的排序顺序呈现数据。例如，您可能想创建上个月所有订单的概览，并首先查看最新的订单。如前所述，SQL Server 中的数据存储在关系中，这是一组无序的（唯一）行。在实际操作中可能并非如此，SQL Server 总是会按主键（使记录唯一的那个）的顺序显示行。这也意味着，每当您查询表时，行几乎总是按该顺序返回。不过有一个陷阱。SQL Server 永远无法保证该顺序。如果您查询数据库一千次，并且返回行的顺序每次都相同，这听起来可能很奇怪。尽管如此，顺序从未得到保证，如果您想要特定的顺序，您将不得不明确指示。这可以通过使用 ORDER BY 子句来完成。

所以让我们看一个例子。以下查询按字母顺序显示所有产品。

SELECT *
FROM Production.Product
ORDER BY Name

查看结果，您可能会注意到这（可能是）第一次结果集不是按 ProductID 排序的。由于我们没有指示排序方向，SQL 以升序对数据进行排序。您可以通过使用 ASC 来显式指示升序，但这并非必需。也可以按降序对数据进行排序。 在这种情况下，您确实必须显式使用排序方向。您可以使用 DESC 关键字来指示降序。以下查询从 Z 到 A 返回所有产品。

SELECT *
FROM Production.Product
ORDER BY Name DESC

也可以按多个列排序。当然，这只有在您按第一列排序时，该列有多个具有相同值的行才有用。如果第一列中的每个值都是唯一的（例如 Name），则无法在此组内对后续列进行排序。以下查询按颜色名称排序，然后按价格降序排序。字母表中我们找到的第一个颜色是 Black。因此，价格最高的黑色产品先返回，然后是第二高的黑色产品等等。在价格最低的黑色产品之后，是价格最高的 Blue 产品等。

SELECT *
FROM Production.Product
ORDER BY Color, ListPrice DESC

运行此查询时，您可能会注意到一些事情。NULL（或无值）并不是具有“最低”值的颜色。它实际上是 NULL（或无值）。 那种 NULL 肯定让事情变得困难，不是吗？幸运的是，这相当明显，即使您没有预料到或不同意它。对“无值”进行排序放在任何值之前。

您可能还记得 ORDER BY 列表也可以包含列别名。这与 SQL Server 内部处理查询的顺序有关。ORDER BY 实际上是唯一在 SELECT 之后（即使 SELECT 出现在查询的第一个关键字处）在内部处理的语句，这使得 ORDER BY 可以使用别名。我将展示一个 ORDER BY 将列别名作为参数的示例。

SELECT
    Name,
    ProductNumber AS Number
FROM Production.Product
ORDER BY Number 

与 WHERE 子句一样，可以对不在 SELECT 列表中的列进行排序。但是，有一项例外。在查询中使用 DISTINCT 时，您不能按不在 SELECT 列表中的列排序。原因是当删除重复项时，结果行不一定能以一对一的方式映射到源行。以下查询显示了这一点。

此查询将返回十行（如前所述），但表包含 504 个唯一名称。SQL Server 无法基于 504 个值对十行进行排序。它应该根据哪些产品名称来排序黑色和黄色的颜色？这可能是一个问题……

也可以按列的（基于 1 的）索引进行排序，但这被认为是不好的做法。以下示例显示了这一点。在此查询中，结果按 Name 排序（因为它是 SELECT 列表中的第一列）。

SELECT Name, ProductNumber
FROM Production.Product
ORDER BY 1

现在想象一下，如果有人更改 SELECT 列表中的顺序，或者在 Name 之前添加列？Name 将不再是 SELECT 列表中的第一列，但 ORDER BY 仍然按第一列排序。这可能会产生意外结果。当在 ORDER BY 中使用列名时，这永远不会是问题。

如果您想指定一个 WHERE 子句和一个 ORDER BY，ORDER BY 应该放在查询的最后。

SELECT *
FROM Production.Product
WHERE Color = 'Red'
ORDER BY Name 

9. 进一步限制结果；TOP 和 OFFSET-FETCH

9.1 TOP

您可能不总是想返回查询中的所有行。例如，因为返回所有行会返回太多行，以至于您的应用程序在获取数据时会响应缓慢数小时。或者，也许您只对最贵的十种产品感兴趣，或者可能是前百分之十。当然，在 SQL 中也可以使用 TOP 语句来实现，用法如下。

SELECT TOP 10 *
FROM Production.Product 

此查询返回前十种产品。但这意义不大。还记得 SQL Server 不保证结果的顺序吗？简单地选择前十名，理论上意味着我们选择了任意十种随机产品。将 TOP 与 ORDER BY 子句结合使用更有用。下一个查询获取价格最高的前十种产品。

SELECT TOP 10 *
FROM Production.Product
ORDER BY ListPrice DESC

运行此查询时，您可能会注意到许多重复的价格。我们正在选择前十名，但实际上接下来的三行（未返回）的价格与最后一行返回的价格相同。即使我们返回了那三行，我们也只会得到三个最高的价格（这可能就是您想要的，也可能不是）。对于这个例子，我们确实想要最贵的产品，即使它们只占三个最高价格（有方法可以实际获取前十个最高价格，但这在此处不作讨论）。但是，我们确实想返回价格与最后一行相同的三个行。 这可以通过向查询添加 WITH TIES 来完成。

SELECT TOP 10 WITH TIES *
FROM Production.Product
ORDER BY ListPrice DESC  

我们还可以使用 TOP 选择一定百分比的结果。这可以通过使用 PERCENT 关键字来完成。

SELECT TOP 10 PERCENT *
FROM Production.Product
ORDER BY ListPrice DESC

此查询返回 51 个结果。产品表实际上有 504 行。504 的百分之十实际上是 50.4。我们不会对 SQL Server 返回 0.4 行感到满意，所以它会返回其上限（始终向上取整）。因此，50.4 变为 51。WITH TIES 也可以添加到查询中，这将返回 55 行而不是 51 行。

我应该提到的一点是，TOP 中的数字可以被参数化。这超出了本文的范围，但我仍然会展示一个示例。

DECLARE @Top AS INT
SET @Top = 10
 
SELECT TOP (@Top) WITH TIES *
FROM Production.Product
ORDER BY ListPrice DESC

9.2 OFFSET-FETCH

如果您想跳过几行而不是选择前几行，该怎么办？这是分页的常见场景。为此，您可以使用 OFFSET-FETCH 子句。让我们跳过价格最高的前十种产品，然后选择接下来的 25 种最贵的产品。使用 OFFSET 我们可以指定要跳过的行数，使用 FETCH 我们可以指定要返回的行数。下面的查询说明了这一点。

SELECT *
FROM Production.Product
ORDER BY ListPrice DESC
OFFSET 10 ROWS FETCH NEXT 25 ROWS ONLY

您可能会注意到几点。首先，我一开始就包含了 ORDER BY。 ORDER BY 实际上在使用 OFFSET-FETCH 时是强制性的。其次，OFFSET-FETCH 子句实际上由两部分组成，即 OFFSET 部分和 FETCH 部分。

让我们先仔细看看这两个部分。如果您想要 FETCH 部分，OFFSET 部分是强制的。但是 FETCH 部分是可选的。不包含 FETCH 部分意味着您跳过一定数量的行并返回所有其余行。您可能想跳过 100 行并返回所有剩余行。

SELECT *
FROM Production.Product
ORDER BY ListPrice DESC
OFFSET 100 ROWS

您也可以指定 OFFSET 为 0。这将选择所有行。

使用 OFFSET 为 0 并指定 FETCH 等同于 TOP 语句。 以下查询使用 OFFSET-FETCH 选择前 25 行。

SELECT *
FROM Production.Product
ORDER BY ListPrice DESC
OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY

您可能会注意到我使用了 FIRST 关键字而不是 NEXT。它们完全可以互换，但既然我们要选择前 25 行，使用 FIRST 而不是 NEXT 可能更正确。ROWS 关键字可以被 ROW 替换，如果您有 OFFSET 或 FETCH 为一，这看起来会更好。

使用 OFFSET-FETCH 时，需要 ORDER BY。由于 SQL Server 不保证顺序，因此使用 ORDER BY 是合乎逻辑的，但也许您确实想在跳过几行随机行之后再选择几行随机行。在这种情况下，您可以使用以下 ORDER BY 子句。

SELECT *
FROM Production.Product
ORDER BY (SELECT NULL)
OFFSET 1 ROW FETCH FIRST 25 ROWS ONLY

由于 OFFSET-FETCH 是 SQL 标准而 TOP 不是，因此建议尽可能使用 OFFSET-FETCH。但是，OFFSET-FETCH 没有 WITH TIES 或 PERCENT 的等效项。因此，如果您需要这些功能，TOP 将是您的唯一选择。

10. 聚合数据；GROUP BY 和 HAVING

很多时候您会想对数据进行分组。您可能想知道您从所有订单、特定年份的所有订单或每位客户的总收入。以下查询仅计算 Sales.SalesOrderHeader 表中的行数。该查询只返回一个值。

SELECT COUNT(*)
FROM Sales.SalesOrderHeader 

COUNT 被称为聚合函数。COUNT(*) 特别指计算行数。此查询所做的是将所有行分组为一个组，然后计算该组的行数。现在，假设您想知道每位客户下了多少订单。我们想为每位客户创建一个组，然后计算该组中的行数。这是通过使用 GROUP BY 子句来实现的。以下查询显示了这种情况。

SELECT CustomerID, COUNT(*)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID 

在此查询中，GROUP BY 子句是必需的。因为 COUNT 只能应用于组，而 CustomerID 本身不是一个组，所以您要么需要删除 CustomerID 以便假定一个组，要么通过使用 GROUP BY 子句显式创建一个按 CustomerID 分组的组。

让我们稍微修改一下最后一个查询，以便它显示我们每个客户有多少销售人员。这可以通过将 COUNT(*) 中的 * 替换为您要计数的列名来完成。

SELECT CustomerID, COUNT(SalesPersonID)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID

花点时间查看结果，并确保结果是正确的。我们看到很多客户没有销售人员。COUNT 会忽略 NULL 值，并且根本不计算它们。那么结果正确吗？不，不正确！例如，ID 为 29484 的客户只有一位销售人员，但我们的结果表明有七位！实际上，COUNT 不计算唯一值的数量，它只是计算值。所以上面的查询给出了每个客户有销售人员的订单数量。幸运的是，我们可以轻松修复这个问题。

SELECT CustomerID, COUNT(DISTINCT SalesPersonID)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID

通过在 COUNT 函数内添加 DISTINCT，我们可以获得所有唯一值的计数（仍排除 NULL 值）。我们也可以基于多个列进行分组。例如，我们想知道每个客户每个送货地址的销售人员数量。在该示例中，我们可以看到一个客户使用一个或多个送货地址，以及服务于一个送货地址的销售人员数量。在此数据库中，每个客户只有一个送货地址，您可以通过运行以下查询来检查。

SELECT
    CustomerID,
    ShipToAddressID,
    COUNT(DISTINCT SalesPersonID)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, ShipToAddressID

我们仍然看到很多没有销售人员的客户。为了我们的目的，我们不关心那些。我们仍然可以使用 WHERE 子句来排除 NULL 值。

SELECT
    CustomerID,
    COUNT(DISTINCT SalesPersonID)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY CustomerID 

请注意，GROUP BY 子句出现在 WHERE 子句之后。所以在这个例子中，WHERE 子句会过滤掉 SalesPersonID 没有值的行，然后对剩余的行进行分组。但是，如果我们想过滤组呢？假设我们只想要拥有一个以上销售人员的客户。WHERE 子句是按行过滤，而不是按组过滤。以下查询不是有效的语法。

错误消息已经给了我们解决方案。我们需要使用 HAVING 子句来过滤组。以下查询解决了我们的问题。

SELECT
    CustomerID,
    ShipToAddressID,
    COUNT(DISTINCT SalesPersonID)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, ShipToAddressID
HAVING COUNT(DISTINCT SalesPersonID) > 1

HAVING 的功能与 WHERE 大致相同，只是它过滤的是组，因此可以使用聚合函数。您可以在同一个查询中使用 WHERE 和 HAVING 子句。但请确保您理解它们之间的区别。

还有其他分组函数。下表列出了最常见的函数。所有这些函数都可以与 DISTINCT 结合使用以仅使用唯一值。与 COUNT 一样，其他函数也忽略 NULL 值。

COUNT(*)    = counts the number of rows
COUNT(column)    = counts the number of values in the column
AVG(column)    = gives the average value of the values in the column
MAX(column)    = gives the maximum value in the column
MIN(column)    = gives the minimum value in the colum
SUM(column)    = adds all values in the column

以下查询显示了这些函数的使用方法。

SELECT
    CustomerID,
    COUNT(*)    AS NoOfOrders,
    AVG(SubTotal)    AS AverageSubTotal,
    MAX(SubTotal)    AS MaxSubTotal,
    MIN(SubTotal)    AS MinSubTotal,
    SUM(SubTotal)    AS TotalSubTotal
FROM Sales.SalesOrderHeader
GROUP BY CustomerID 

在最后一个示例中，我对所有聚合列都使用了别名。您可能已经注意到，SQL Server 无法为聚合列命名。建议为这些列设置别名。当我们按聚合列对结果进行排序时，我们现在有两个选择。我们可以按聚合函数排序，这会使我们的查询显得杂乱，或者我们可以按别名排序。以下查询显示了区别。

SELECT
    CustomerID,
    COUNT(*)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY COUNT(*)
 
SELECT
    CustomerID,
    COUNT(*) AS NoOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY NoOfOrders

与 DISTINCT 一样，ORDER BY 子句现在不能包含不在 SELECT 列表中的列。 

11. 从多个表中选择；使用 JOIN

您可能已经注意到，在上一部分中，我们使用了 CustomerID 和 SalesPersonID，但不知道这些 ID 的含义。SalesOrderHeader 表中的 CustomerID 在 Customer 表中有一个对应的行，由外键约束强制执行，我们可以在其中获取有关客户的更多信息。以 SalesOrderHeader 表中的第一个订单为例。

SELECT TOP 1
    SalesOrderID,
    SalesOrderNumber,
    CustomerID,
    SubTotal
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID

如果我们想知道这个客户的名字，我们可以通过一个单独的查询来查找并获取具有该 ID 的客户。但是，我们不想要这样做。我们需要在与订单信息相同的 result set 中显示客户的姓名。我们可以使用两种方法从多个表中选择。

从多个表中选择的第一种方法（不推荐）是将更多表添加到 FROM 子句。以下示例显示了如何执行此操作。请勿运行以下查询。

SELECT *
FROM Sales.SalesOrderHeader, Sales.Customer

此查询返回 SalesOrderHeader 和 Customer 表的笛卡尔积。这意味着每个订单都与每个客户结合。我的机器上运行此查询将花费数小时，并且应返回 623636300 行（31456 个订单 * 19820 个客户）。这显然不是我们想要的。我们只想返回笛卡尔积的子集。那么，我们想看到哪些行呢？我们只想返回 Customer 表中的 CustomerID 与 SalesOrderHeader 表中的 CustomerID 匹配的行。使用 WHERE 子句可以相对简单地完成此操作。请注意，如果您有订单的 CustomerID 没有值，则该订单将不会返回。

SELECT *
FROM Sales.SalesOrderHeader, Sales.Customer
WHERE Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID 

既然我们正在从多个表中选择，我们可能需要使用包含表名的列名。毕竟，如果我们只指定 CustomerID，我们指的是 SalesOrderHeader 表的 CustomerID 还是 Customer 表的 CustomerID？这时表别名就派上用场了。您已经看到了别名的语法，所以这应该不是问题。这是使用别名编写的查询。

SELECT *
FROM Sales.SalesOrderHeader AS s, Sales.Customer AS c
WHERE s.CustomerID = c.CustomerID

请注意，对于表别名，我们使用简短的名称，如单个字符（例如表名的第一个字母）。使用表别名时，必须使用别名。使用完整的表名不再允许。在某些情况下，别名是强制性的，例如当您两次从同一表进行选择时。不幸的是，Customer 表没有我们想要的 Name。Customer 有一个 PersonID，它对应于 Person 表中的一行。这个 Person 拥有我们需要的姓名。所以我们需要另一个表来选择，并确保我们在 WHERE 子句中只选择正确的个人。

SELECT *
FROM Sales.SalesOrderHeader AS s,
    Sales.Customer AS c,
    Person.Person    AS p
WHERE s.CustomerID = c.CustomerID
    AND c.PersonID = p.BusinessEntityID 

因此，我们现在从 SalesOrderHeader、Customer 和 Person 表中选择所有列。如您所见，我们的结果有多个同名字段，如 CustomerID、rowguid 和 ModifiedDate。所以，让我们只选择我们想要的列。

SELECT
    s.SalesOrderID,
    s.SalesOrderNumber,
    s.CustomerID,
    p.FirstName,
    p.LastName,
    s.SubTotal
FROM Sales.SalesOrderHeader AS s,
    Sales.Customer AS c,
    Person.Person    AS p
WHERE s.CustomerID = c.CustomerID
    AND c.PersonID = p.BusinessEntityID  

如前所述，这不是从多个表中选择的推荐方法，所以我将不再详细介绍。

11.1 CROSS JOIN

相反，我们应该使用 JOIN 运算符。有多种 JOIN 类型。最简单的是 CROSS JOIN，它返回笛卡尔积。再次强调，请勿运行此查询，因为它将返回超过 6 亿条记录并花费数小时（除非您添加 where 子句）。

SELECT *
FROM Sales.SalesOrderHeader
    CROSS JOIN Sales.Customer

11.2 INNER JOIN

下一个 JOIN，也是您最常使用的，是 INNER JOIN。实际上 INNER JOIN 是最常用的 JOIN，并且 INNER 关键字是可选的。INNER JOIN 运算符使用 ON 子句匹配行，而不是 WHERE。以下示例说明了这一点。

SELECT *
FROM Sales.SalesOrderHeader AS s
    INNER JOIN Sales.Customer AS c ON s.CustomerID = c.CustomerID 

使用 INNER JOIN 时，主表中没有至少一个匹配项的行将被丢弃。当连接表有多个匹配项时，主表中的行将在结果中重复。以下查询说明了行被丢弃。SalesOrderHeader 表中的 SalesPersonID 列包含 NULL 值。显然，SalesPersonID 没有值的行无法与 SalesPerson 表中的销售人员匹配。如果您运行以下查询，您会发现这些行不包含在结果中。

SELECT *
FROM Sales.SalesOrderHeader
    JOIN Sales.SalesPerson ON SalesPersonID = BusinessEntityID

以下查询显示 SalesOrderHeader 行在与 SalesOrderDetail 表连接时会重复。一个订单可以有多个明细，并且对于每个明细，销售订单头都会重复。

SELECT *
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID
ORDER BY soh.SalesOrderID

ON 子句可以包含更高级的过滤器，就像 WHERE 子句一样。例如，我们可以仅基于有折扣的明细进行连接。

SELECT *
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID
                        AND sod.UnitPriceDiscount > 0
ORDER BY soh.SalesOrderID 

一个警觉的读者可能会注意到，sod.UnitPriceDiscount > 0 部分也可以放在查询的 WHERE 子句中。确实，在 INNER JOIN 中，ON 和 WHERE 子句是可互换的。以下查询等同于上面的查询。

SELECT *
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID
WHERE sod.UnitPriceDiscount > 0
ORDER BY soh.SalesOrderID

任何没有与其他表中的行匹配的行都会在结果中被丢弃，因此，无论 ON 子句过滤了 SalesOrderDetail 表中的一行（然后该行也从 SalesOrderHeader 表中丢弃），还是在 ON 子句中找到匹配项后 WHERE 子句过滤了行，都没关系。 

使用 INNER JOINS 时，ON 子句始终是强制性的。这并不意味着您必须检查两个行之间的相等性。以下内容也是完全有效的语法，并且等同于 CROSS JOIN（或笛卡尔积），因为 SalesOrderDetail 表中的每一行都与 SalesOrderHeader 表中的任何行匹配（因为 1 = 1 谓词始终返回TRUE）。

SELECT *
FROM Sales.SalesOrderHeader
    JOIN Sales.SalesOrderDetail ON 1 = 1

11.3 OUTER JOIN

我们刚刚看到，在使用 INNER JOIN 时，没有在连接表中找到匹配项的行会被丢弃。使用 OUTER JOIN，我们可以保留这些结果。有三种 OUTER JOIN：一种在未找到匹配项时保留主表中的行，一种保留连接表中的行，还有一种保留两者。让我们再次将 SalesOrderHeader 表与 SalesPerson 表连接，但这次我们想保留没有销售人员的销售订单。

SELECT *
FROM Sales.SalesOrderHeader
    LEFT OUTER JOIN Sales.SalesPerson ON SalesPersonID = BusinessEntityID  

如您所见，LEFT OUTER JOIN 在找不到 SalesPerson 行时保留 SalesOrderHeaders（JOIN 的 LEFT 部分）。在结果集中，所有 SalesPerson 列对于这些行都为空值。

以下查询将订单与客户连接。并非每个客户都下了订单，所以我们可以选择将这些客户保留在结果集中，并为这些客户返回空的订单。

SELECT *
FROM Sales.SalesOrderHeader AS soh
    RIGHT OUTER JOIN Sales.Customer AS c ON c.CustomerID = soh.CustomerID
ORDER BY c.CustomerID 

如您所见，RIGHT OUTER JOIN 在找不到 SalesOrderHeaders 行时保留 Customers（JOIN 的 RIGHT 部分）。

最后一种 OUTER JOIN 是 FULL OUTER JOIN，它保留 JOIN 两边的行，所以它基本上既是 LEFT OUTER JOIN 也是 RIGHT OUTER JOIN。在 SalesOrderHeader 表中，有些订单有 CurrencyRateID，但也有一些订单没有 CurrencyRateID。这至少可以符合 LEFT OUTER JOIN 的条件，但也有一些 CurrencyRates 没有订单，这也符合表之间的连接作为 RIGHT OUTER JOIN 的条件。在这种情况下，如果我们想保留没有货币汇率的订单和没有订单的货币汇率，我们可以使用 FULL OUTER JOIN。

SELECT *
FROM Sales.SalesOrderHeader AS soh
    FULL OUTER JOIN Sales.CurrencyRate AS c ON c.CurrencyRateID = soh.CurrencyRateID
ORDER BY soh.SalesOrderID

在所有 OUTER JOINS 中，OUTER 关键字是可选的。所以 LEFT JOIN、RIGHT JOIN 和 FULL JOIN 也是有效的语法。

请记住，对于 INNER JOIN，ON 子句和 WHERE 子句具有相同的过滤功能？然而，对于 OUTER JOIN，情况并非如此。OUTER JOIN 会丢弃连接的一侧的行，但会保留另一侧的行。因此，ON 子句仅用于匹配目的。当向 ON 子句添加额外的谓词时，它只是意味着如果谓词返回 FALSE，则连接的右侧或左侧将为空值（与 INNER JOIN 不同，后者会将整个结果丢弃）。例如，以下两个查询都返回截然不同的结果。

SELECT *
FROM Sales.SalesOrderHeader AS soh
    FULL JOIN Sales.CurrencyRate AS c ON c.CurrencyRateID = soh.CurrencyRateID
                        AND c.AverageRate > 1
ORDER BY c.AverageRate
 
SELECT *
FROM Sales.SalesOrderHeader AS soh
    FULL JOIN Sales.CurrencyRate AS c ON c.CurrencyRateID = soh.CurrencyRateID
WHERE c.AverageRate > 1
ORDER BY c.AverageRate 

第一个查询返回所有订单，但如果订单的平均汇率高于 1，则丢弃该订单的货币汇率。第二个查询仅返回平均汇率高于 1 的订单。

11.4 自连接 

一个表可以与自身进行连接。您可能想要这样做的原因可能包括：一个Employee有一个ManagerID，它对应于也是Employee的经理。不幸的是，我在 AdventureWorks2012 数据库中找不到一个有意义的自连接示例。 但这并不意味着我不能给您展示。下一个示例从 Person.Person 表中选择，并与 Person.Person 表连接，选择 Persons ID + 1 的 Person。当然，这并不是一个真正的有效业务场景，并且当 ID 不连续时连接会失败，但它展示了如何将一个表与其自身连接。

SELECT
    p1.BusinessEntityID    AS CurrentID,
    p1.Title        AS CurrentTitle,
    p1.FirstName        AS CurrentFirstName,
    p1.LastName        AS CurrentLastName,
    p2.BusinessEntityID    AS NextID,
    p2.Title        AS NextTitle,
    p2.FirstName        AS NextFirstName,
    p2.LastName        AS NextLastName
FROM Person.Person AS p1
    LEFT JOIN Person.Person AS p2 ON p2.BusinessEntityID = p1.BusinessEntityID + 1
ORDER BY CurrentID, CurrentFirstName, CurrentLastName 

11.5 多个 JOIN 

在前面的示例中，我们基于 SalesOrderHeader 中的 CustomerID 选择 Customer 数据，然后使用 Customer 表中的 PersonID 选择 Person 数据。我们也可以为此任务使用 JOIN。实际上，可以在单个查询中使用各种类型的连接。请注意，一个 JOIN 可能会影响另一个。在下面的示例中，您可能会认为只选择了最终具有人员的客户的订单（因为与 Person 的 JOIN 是 INNER JOIN）。但是，因为与客户的 JOIN 是 RIGHT JOIN，并且人员是基于客户连接的，所以并非每个人都有订单。

SELECT
    s.SalesOrderID,
    s.SalesOrderNumber,
    c.CustomerID,
    p.BusinessEntityID,
    p.FirstName,
    p.LastName,
    s.SubTotal
FROM Sales.SalesOrderHeader AS s
    RIGHT JOIN Sales.Customer AS c ON c.CustomerID = s.CustomerID
    JOIN Person.Person AS p ON p.BusinessEntityID = c.CustomerID
ORDER BY p.BusinessEntityID 

12. 多个分组；GROUPING SETS

12.1 GROUPING SETS

我们稍微暂停了一下分组运算符，以探索我们可用的 JOIN 选项。但是，分组的内容比我们迄今为止讨论的要多。一个查询可以在一个查询中包含多个分组。我们可能想知道订单总数（或平均值、最大值、最小值或总和）、每个客户的订单数、每个销售人员的订单数，以及每个客户和销售人员的订单数。这可以通过 GROUPING SETS 子句来实现。下面的查询显示了一个示例。

SELECT
    CustomerID,
    SalesPersonID,
    COUNT(*) AS NoOfOrders
FROM Sales.SalesOrderHeader
GROUP BY GROUPING SETS
(
    (CustomerID            ),
    (SalesPersonID            ),
    (CustomerID, SalesPersonID    ),
    (                )
)
ORDER BY SalesPersonID,
    CustomerID  

此查询的结果有点难以解释。最初的结果如下所示

我们看到的第一条结果是订单的总数。第二行看起来具有相同的数据，但 NoOfOrders 与第一行不同。这实际上是没有销售人员（SalesPersonID 为 NULL）的订单数。之后的行看起来完全相同。一个代表特定客户和特定 SalesPersonID（碰巧是 NULL）的销售订单，另一个代表特定客户的销售订单，而不考虑 SalesPersonID。

当我们查看 SalesPersonID 实际具有值的后续结果时，这可能会更清楚。

在这里，我们看到前三行包含特定客户和特定销售人员的订单数。第四行没有客户，因此显示该销售人员的订单数（不考虑客户）。接下来的行分解了他按客户的销售数据，直到出现另一行，其中客户为空，然后下一个销售人员开始。

如果 SalesPersonID 没有值，我们如何知道它是否属于一个组？您可能会想从查询中删除没有销售人员的订单，但要小心！下面的查询更好地概览了每个销售人员的销售情况，但许多客户的总数和总计都不正确了！毕竟，没有销售人员的订单不会计入客户和总计。 这可能就是您想要的，也可能不是。您已被警告。我将在本节稍后回到这个问题。

SELECT
    CustomerID,
    SalesPersonID,
    COUNT(*) AS NoOfOrders
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY GROUPING SETS
(
    (CustomerID            ),
    (SalesPersonID            ),
    (CustomerID, SalesPersonID    ),
    (                )
)
ORDER BY SalesPersonID,
    CustomerID

检查这是否确实不是您期望的结果。订单总数突然缩减到只有 3806 个，但这仅仅是有销售人员的订单总数！这可能就是您想要的，也可能不是。您已被警告。我将在本节稍后回到这个问题。

12.2 CUBE

CUBE 子句是 GROUPING SET 子句的快捷方式。在 CUBE 子句中，您可以指定要分组的列，CUBE 会创建所有可能的集合，包括空集。前面查询的 CUBE 等效项如下。

SELECT
    CustomerID,
    SalesPersonID,
    COUNT(*) AS NoOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CUBE(CustomerID, SalesPersonID)
ORDER BY SalesPersonID,
    CustomerID  

CUBE 现在按无分组（所以计算所有行），按 CustomerID 分组（计算每位客户的行数），按 SalesPersonID 分组（计算每个销售人员的行数），并按 CustomerID 和 SalesPersonID 的组合分组（所以计算特定销售人员的客户的行数）。您可以检查结果是否与上面的查询（不过滤没有销售人员的行）相同。

12.3 ROLLUP

ROLLUP 比 CUBE 稍微复杂一些。与 CUBE 一样，它也是 GROUPING SET 子句的快捷方式，但与 CUBE 不同的是，当您需要特定的层次结构时可以使用它。例如，每个订单都有一个 BillToAddressID。每个 BillToAddressID 都映射到 Person.Address 表中的一行。Address 表有一个 City 字段和一个 StateProviceID，它映射到 Person.StateProvice 表中的一行。StateProvince 表有一个 Name 和一个 CountryRegionCode，它映射到 Person.CountryRegion 表中的一行，该表有一个 Name。因此，通过执行几个 JOIN，我们可以为订单的每个账单地址获取 City、StateProvinceName 和 CountryName。下面的查询显示了如何实现这一点。

SELECT
    soh.SalesOrderID,
    soh.SalesOrderNumber,
    soh.BillToAddressID,
    a.City,
    sp.Name            AS StateProvinceName,
    cr.Name            AS CountryName
FROM Sales.SalesOrderHeader AS soh
    JOIN Person.Address AS a ON a.AddressID = soh.BillToAddressID
    JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID
    JOIN Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode
ORDER BY CountryName,
    StateProvinceName,
    City

这个查询实际上并不难。如果您不能立即理解它，请花点时间将其分解成小块（一次一个 JOIN）。

我们需要知道按国家/地区、按省份、按城市收到了多少订单。但是，可能存在多个国家/地区或省份具有相同的城市。所以，如果我们不知道城市所在的省份和国家，计算一个城市中的订单数量就没有意义了。这创建了一个层次结构。以下使用 GROUPING SET 的示例正是我所说的。 

SELECT
    a.City,
    sp.Name            AS StateProvinceName,
    cr.Name            AS CountryName,
    COUNT(*)        AS NoOfOrder
FROM Sales.SalesOrderHeader AS soh
    JOIN Person.Address AS a ON a.AddressID = soh.BillToAddressID
    JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID
    JOIN Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode
GROUP BY GROUPING SETS
(
    (cr.Name, sp.Name, a.City    ),
    (cr.Name, sp.Name        ),
    (cr.Name            ),
    (                )
)
ORDER BY CountryName,
    StateProvinceName,
    City 

我们不单独按 City 或 Province 分组，仅仅因为没有省份和/或国家/地区，它们就没有意义。尽管概念可能有点令人困惑，但结果实际上相当直接。

从第一行读取，总共下了 31465 个订单。第二行表明其中 6843 个订单来自澳大利亚。下一行表明有 3009 个订单来自新南威尔士州（澳大利亚）。接下来的几行继续显示每个城市（位于新南威尔士州，澳大利亚）的订单数量。直到第 22 行，其中 City 为空，并且开始显示昆士兰省（澳大利亚）。在计算完澳大利亚的所有城市和省份后，我们转向下一个国家/地区，加拿大。一直到美国怀俄明州的 Rock Springs。  

现在让我们看看使用 ROLLUP 的快捷方式。

SELECT
    a.City,
    sp.Name            AS StateProvinceName,
    cr.Name            AS CountryName,
    COUNT(*)        AS NoOfOrder
FROM Sales.SalesOrderHeader AS soh
    JOIN Person.Address AS a ON a.AddressID = soh.BillToAddressID
    JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID
    JOIN Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode
GROUP BY ROLLUP (cr.Name, sp.Name, a.City)
ORDER BY CountryName,
    StateProvinceName,
    City 

您可以检查结果是否与之前使用 GROUPING SETS 的查询相同。

12.4 GROUPING

我想解决我们在使用分组时遇到的一个问题。我们在第一个示例（和 CUBE 示例）中使用的查询返回了两行，其中 CustomerID 和 SalesPersonID 都没有值。一行代表所有订单，另一行代表没有销售人员的订单。我们如何知道哪一行是哪一行呢？为此，我们可以使用 GROUPING 和 GROUPING_ID。

GROUPING 函数接受一个列作为参数，如果该列不是分组的一部分，则返回 1，如果是分组的一部分，则返回 0。这可能有些反直觉。1 表示不包含，0 表示**包含**。下面的示例展示了如何使用此函数。

SELECT
    CustomerID,
    SalesPersonID,
    GROUPING(SalesPersonID)    AS IsSalesPersonNotPartOfGroup,
    COUNT(*)        AS NoOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CUBE(CustomerID, SalesPersonID)
ORDER BY SalesPersonID,
    CustomerID,
    IsSalesPersonNotPartOfGroup 

结果并没有变得更容易阅读一些。

我们现在可以看到一个组是否包含 SalesPersonID。通过此函数，我们还可以排除 SalesPersonID 是分组的一部分但又没有值的行。要实现这一点，我们只需要包含 IsSalesPersonNotPartOfGroup = 1 的组，或者 IsSalesPersonNotPartOfGroup = 0 且 SalesPersonID IS NOT NULL 的组。下面的查询演示了这一点（请注意，我没有选择 IsSalesPersonNotPartOfGroup）。

SELECT
    CustomerID,
    SalesPersonID,
    COUNT(*) AS NoOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CUBE(CustomerID, SalesPersonID)
HAVING GROUPING(SalesPersonID) = 1
    OR (GROUPING(SalesPersonID) = 0
    AND SalesPersonID IS NOT NULL)
ORDER BY CustomerID,
    SalesPersonID  

运行此查询并检查结果。值得注意的是，销售订单的总数现在是正确的（我们无法使用 WHERE 子句来实现这一点）。

12.5 GROUPING_ID

另一个可以使用的函数是 GROUPING_ID。此函数通过根据列是否为分组的一部分来打开或关闭位来工作。下面的查询显示了一个示例。

SELECT
    GROUPING_ID(CustomerID, SalesPersonID) AS GroupID,
    CustomerID,
    SalesPersonID,
    COUNT(*) AS NoOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CUBE(CustomerID, SalesPersonID)
ORDER BY SalesPersonID,
    CustomerID

现在，这实际上并没有您希望的那么实用和简单。我们将两个参数传递给 GROUPING_ID，这意味着 GROUPING_ID 使用两个位来打开或关闭它们。如果 SalesPersonID 是组的一部分，那么位于参数列表中与 SalesPersonID 相同位置（最右边）的位将被打开。结果显示了它的样子。

第一行显示 GroupID 2。这需要了解位运算。我们将两个参数传递给 GROUPING_ID，因此它有两个位，即 00。当一列不是组的一部分时，位将被打开。在这种情况下，位表示的 2 是 10，这意味着 CustomerID 不是组的一部分，而 SalesPersonID 是。下一个值是 3，或位表示的 11，这意味着两个列都不是组的一部分。下一个值 0（位表示的 00）表示两个列都是组的一部分。下一个值 1（位表示的 01）表示 SalesPersonID 不是组的一部分。

13. 窗口函数；OVER 子句

有时您想显示分组数据的结果，例如订单数量，而不实际在结果中对数据进行分组。这是使用窗口函数实现的。窗口函数的工作方式与我们之前看到的 GROUP BY 子句类似，只是它计算结果并返回单个值，从而可以在不分组查询的情况下使用这些值。 '窗口'由您的结果集定义。因此，结果集被传递给窗口函数，窗口函数执行计算（通过分组和排序窗口），并为实际结果集中的每一行返回一个单个值。

窗口函数在 WHERE 和 HAVING 子句之后内部执行。这意味着您不能在除 ORDER BY 之外的任何过滤器或其他子句中使用它们。通用表表达式或CTE提供了解决方案，但在本文的第二部分才会讨论它们。 

13.1 聚合函数

我们可以使用 COUNT、AVG、MAX、MIN 和 SUM 聚合函数在窗口中使用，并通过使用 OVER 子句将它们应用于结果集中的每一行。下面的查询显示了每个函数的示例。

 SELECT
    SalesOrderID,
    SalesOrderNumber,
    COUNT(*) OVER()            AS NoOfOrders,
    COUNT(SalesPersonID) OVER()    AS OrdersWithSalesPerson,
    AVG(SubTotal) OVER()        AS AvgSubTotal,
    MAX(SubTotal) OVER()        AS MaxSubTotal,
    MIN(SubTotal) OVER()        AS MinSubTotal,
    SUM(SubTotal) OVER()        AS TotalSubTotal
FROM Sales.SalesOrderHeader 

正如您所见，我们可以在不分组 SalesOrderID 和 SalesOrderNumber 的情况下应用聚合函数。现在，每一行都显示了订单数量、有销售人员的订单数量以及小计的平均值、最大值、最小值和总计。

这非常有用，但通常您希望按组显示这些结果。例如，您需要在行中显示客户的总小计。这可以通过在 OVER 子句中使用 PARTITION BY 来完成。下面的查询展示了如何使用它。

SELECT
    SalesOrderID,
    SalesOrderNumber,
    CustomerID,
    SUM(SubTotal) OVER(PARTITION BY CustomerID) AS TotalSubTotalPerCustomer,
    SUM(SubTotal) OVER() AS TotalSubTotal
FROM Sales.SalesOrderHeader  

查看结果时，您会发现 TotalSubTotal 对每一行都相同，但 TotalSubTotalPerCustomer 对于具有相同客户的每一行都相同，而对于具有不同客户的每一行则不同。 您的结果集或窗口按 CustomerID（在 PARTITION BY 中定义）进行分组，并为每一行返回一个单一结果。

您可以指定多个列进行分区。例如，您可以显示每个客户每个销售人员的总小计。

SELECT
    SalesOrderID,
    SalesOrderNumber,
    CustomerID,
    SalesPersonID,
    SUM(SubTotal) OVER(PARTITION BY CustomerID, SalesPersonID)
                AS TotalSubTotalPerCustomerPerSalesPerson,
    SUM(SubTotal) OVER() AS TotalSubTotal
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, SalesPersonID 

13.2 框架

窗口聚合函数支持另一种称为框架的过滤选项。我花了很长时间才弄明白这个概念，所以我会尽量用简单的方式来解释。在前面的示例中，我们看到窗口函数可以计算一个值，例如列的总和，用于特定组，例如客户。使用框架函数，可以使此计算依赖于前面的行或后面的行。这使得显示累积结果成为可能。让我给您举个例子。

SELECT
    SalesOrderID,
    SalesOrderNumber,
    OrderDate,
    CustomerID,
    SubTotal,
    SUM(SubTotal) OVER(PARTITION BY CustomerID
                ORDER BY OrderDate
                ROWS BETWEEN UNBOUNDED PRECEDING
                AND CURRENT ROW) AS TotalSubTotalSoFarForCustomer
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate  

因此，此查询再次给出了 SubTotal 列的总和，但它给出了当前组中所有先前行的总和，而不是所有行的总和。这由 ROWS BETWEEN UNBOUNDED PRECEDING 部分指示，意思是累加当前组中所有先前行的所有小计。最后一部分 AND CURRENT ROW 意思是直到当前行。ORDER BY 子句是必需的，因为如果没有它，ROWS BETWEEN ... AND ... 将毫无意义（因为它将意味着任意随机行）。所以，让我们看看上面查询的结果。

您可以看到，在第一行中，SubTotal 和 TotalSubTotalSoFarForCustomer 的值相同。在第二行中，TotalSubTotalSoFarForCustomer 的值是上一行的值加上当前行的 SubTotal。下一行的值又是上一行的值加上当前行的 SubTotal。下一行开始了一个新组（新的 CustomerID），因此 SubTotal 和 TotalSubTotalSoFarForCustomer 再次相等。因此，使用此语法，我们创建了累积结果。

我应该提到，我按 OrderDate 对分区进行了排序。这意味着窗口在进行任何计算之前首先按此列排序。这并不意味着您的实际结果集以相同的方式排序。如果我在查询末尾没有添加 ORDER BY CustomerID, OrderDate，结果看起来会有些奇怪（尽管它们仍然是正确的）。

可以在单个查询中使用更多框架函数，并且可以根据需要对它们进行分区和排序。但是，如果您运行以下查询，您会发现以逻辑顺序呈现这些数据很困难甚至不可能。 

SELECT
    SalesOrderID,
    SalesOrderNumber,
    OrderDate,
    CustomerID,
    SalesPersonID,
    DueDate,
    SubTotal,
    SUM(SubTotal) OVER(PARTITION BY CustomerID
                ORDER BY OrderDate
                ROWS BETWEEN UNBOUNDED PRECEDING
                AND CURRENT ROW) AS TotalSubTotalSoFarForCustomer,
    MAX(SubTotal) OVER(PARTITION BY SalesPersonID
                ORDER BY DueDate
                ROWS BETWEEN UNBOUNDED PRECEDING
                AND CURRENT ROW) AS MaxSubTotalBySalesPersonSoFar
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate

您也可以将 UNBOUNDED PRECEDING 和 CURRENT ROW 互换。这样做将导致累积行的逆过程。第一行现在将是该客户所有行的总和，而第二行将是第一行的值减去第一行的 SubTotal。

SELECT
    SalesOrderID,
    SalesOrderNumber,
    OrderDate,
    CustomerID,
    SubTotal,
    SUM(SubTotal) OVER(PARTITION BY CustomerID
                ORDER BY OrderDate
                ROWS BETWEEN CURRENT ROW
                AND UNBOUNDED FOLLOWING) AS InverseCumulative
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate 

请注意，我们使用 FOLLOWING 而不是 PRECEDING，因为现在我们需要查看后面的行而不是前面的行。

我们还可以指定要回顾或展望的行数。以下查询给出了上一行和当前行的总和（即使当前行之前还有更多行）。 

SELECT
    SalesOrderID,
    SalesOrderNumber,
    OrderDate,
    CustomerID,
    SubTotal,
    SUM(SubTotal) OVER(PARTITION BY CustomerID
                ORDER BY OrderDate
                ROWS BETWEEN 1 PRECEDING
                AND CURRENT ROW) AS SumOfLastAndCurrentRowForCustomer
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate

PARTITION BY 在框架函数中是可选的。以下查询仅返回一个与客户无关的累积值。请注意，我也从 ORDER BY 子句中删除了 CustomerID。

SELECT
    SalesOrderID,
    SalesOrderNumber,
    OrderDate,
    CustomerID,
    SubTotal,
    SUM(SubTotal) OVER(ORDER BY OrderDate
                ROWS BETWEEN UNBOUNDED PRECEDING
                AND CURRENT ROW) AS TotalSubTotalSoFar
FROM Sales.SalesOrderHeader
ORDER BY OrderDate 

13.3 排名函数

排名函数可以根据指定的排序在窗口中对行进行排名。SQL Server 中有四个排名函数：ROW_NUMBER、RANK、DENSE_RANK 和 NTILE。ORDER BY 子句是必需的，而 PARTITION 不是（如果未指定，则整个窗口被视为一个组）。下面的示例展示了如何使用这些函数。 

SELECT
    SalesOrderID,
    SalesOrderNumber,
    CustomerID,
    ROW_NUMBER() OVER(ORDER BY CustomerID) AS RowNumber,
    RANK() OVER(ORDER BY CustomerID) AS [Rank],
    DENSE_RANK() OVER(ORDER BY CustomerID) AS DenseRank,
    NTILE(5000) OVER(ORDER BY CustomerID) AS NTile5000
FROM Sales.SalesOrderHeader
ORDER BY CustomerID  

ROW_NUMBER 不言而喻。它只是返回当前行的编号。
RANK 和 DENSE_RANK 为具有唯一排序值的每一行分配一个数字。这意味着具有相同排序值（在此情况下为 CustomerID）的行获得相同的数字。RANK 和 DENSE_RANK 之间的区别在于，RANK 将当前排名编号加上具有相同排序值的行数分配给下一行，而 DENSE_RANK 始终分配前一个排名编号加一，而不管有多少行具有相同的排序值。
NTILE 将行分区或分块为大小相等的组。在这种情况下，返回的结果有 31465 行，我们请求了 5000 个大小相等的块。31456 除以 5000 等于 6，余数为 1456。这意味着 NTILE 值在每六行之后增加。由于余数为 1456，因此前 1456 个块会额外增加一行。

以下结果显示了排名函数输出。

在这种情况下，按 CustomerID 排序不是唯一的。因此，SQL Server 不保证此窗口内的顺序，您的行号也不保证。这意味着下次运行查询时，现在是第二行的记录可能成为第一行或第三行。如果您想保证行编号可重复，您应该添加另一个排序列，使排序唯一，例如 SalesOrderID。这适用于所有窗口函数，包括聚合函数、框架和偏移函数。

SELECT
    SalesOrderID,
    SalesOrderNumber,
    CustomerID,
    ROW_NUMBER() OVER(ORDER BY CustomerID, SalesOrderID) AS RowNumber,
    RANK() OVER(ORDER BY CustomerID) AS [Rank],
    DENSE_RANK() OVER(ORDER BY CustomerID) AS DenseRank,
    NTILE(5000) OVER(ORDER BY CustomerID, SalesOrderID) AS NTile5000
FROM Sales.SalesOrderHeader
ORDER BY CustomerID

请注意，在最后一个示例中，我没有为 RANK 函数添加 SalesOrderID 排序。这些函数返回的对于具有相同 CustomerID 的行是相同的值，无论它们在其组内的顺序如何。添加额外的排序列实际上会改变 RANK 函数的含义！

13.4 偏移函数

偏移函数可以返回窗口中第一行或最后一行中的值，或者从当前行偏移指定行数的行中的值。有四个偏移函数：LAG、LEAD、FIRST_VALUE 和 LAST_VALUE。

LAG 和 LEAD 的工作方式基本相同，只是 LAG 查看当前行之前的行，而 LEAD 查看当前行之后的行。语法实际上与聚合函数非常相似。下一个查询显示了这一点。

SELECT
    SalesOrderID,
    SalesOrderNumber,
    LAG(CustomerID) OVER(ORDER BY OrderDate) AS PreviousCustomer,
    CustomerID AS CurrentCustomer,
    LEAD(CustomerID) OVER(ORDER BY OrderDate) AS NextCustomer
FROM Sales.SalesOrderHeader
ORDER BY OrderDate

如果您运行此查询，您将看到每一行都有一个值指示前一个订单的客户，以及一个值指示下一个订单的客户。注意您的排序！如果您在窗口函数或结果集中的排序不正确，使用此类函数就没有意义。我们在这里看到结果。

您可以看到第一行没有前一个客户的值。那是因为没有前一行，所以也没有前一个客户。您会在最后一行看到与下一个客户类似的情况。

那么，如果我们想查看两个或更多行之后呢？我们可以将第二个参数传递给 LAG 和 LEAD 函数，指定要跳过的行数。下面的查询向我们展示了客户订单频率的一些信息。我们显示了客户倒数第二个订单（如果存在）的装运日期，以及客户下一个订单（如果存在）之后的装运日期。

SELECT
    SalesOrderID,
    SalesOrderNumber,
    CustomerID,
    OrderDate,
    ShipDate,
    LAG(ShipDate, 3) OVER(PARTITION BY CustomerID
                ORDER BY ShipDate) AS ShipDateThreeOrdersAgo,
    LEAD(ShipDate, 2) OVER(PARTITION BY CustomerID
                ORDER BY ShipDate) AS ShipDateOfOrderAfterNext
FROM Sales.SalesOrderHeader
ORDER BY OrderDate

请注意，我按订单日期对结果集进行排序，而按装运日期对窗口进行排序。在这种情况下，这实际上并不重要。查看单行时，我可以简单地看到该客户是否至少有三个订单以及何时发货。并且如果该客户在此之后至少有两个订单，并且何时发货。

我们还可以处理指定行未找到时出现的 NULL 值。LAG 和 LEAD 函数可以接受第三个参数，该参数指定 NULL 的占位符。确保您指定与列相同类型的值，否则会收到错误，除非您进行 CAST（这将在本文的下一部分讨论）。下面的查询显示了每个客户的前一个或下一个销售订单号。当没有前一个或下一个订单时，将显示文本 "No previous/next orders"（而不是 NULL）。

SELECT
    SalesOrderID,
    CustomerID,
    LAG(SalesOrderNumber, 1, 'No previous orders')
        OVER(PARTITION BY CustomerID
        ORDER BY OrderDate) AS PreviousOrderForCustomer,
    SalesOrderNumber AS CurrentOrderNumber,
    LEAD(SalesOrderNumber, 1, 'No next orders')
        OVER(PARTITION BY CustomerID
        ORDER BY OrderDate) AS NextOrderForCustomer
FROM Sales.SalesOrderHeader
ORDER BY OrderDate

现在让我们看看 FIRST_VALUE 和 LAST_VALUE。函数名称不言而喻。这些函数返回窗口中第一行或最后一行指定的值。

SELECT
    SalesOrderID,
    CustomerID,
    SalesOrderNumber AS CurrentOrder,
    FIRST_VALUE(SalesOrderNumber) OVER(ORDER BY SalesOrderNumber) AS FirstOrder,
    LAST_VALUE(SalesOrderNumber) OVER(ORDER BY SalesOrderNumber) AS LastOrder
FROM Sales.SalesOrderHeader
ORDER BY OrderDate 

正如您在结果中看到的，每一行现在都显示了第一个和最后一个订单号。但是，最后一个订单号有些奇怪，它总是具有当前行的值，好像它是正在评估的最后一行。 实际上确实如此。与聚合函数一样，FIRST_VALUE 和 LAST_VALUE 支持框架。因此，如果我们想显示最后一个订单，我们就必须通过框架显式地指示它。正如您所见，下面的查询返回了预期的结果。

SELECT
    SalesOrderID,
    CustomerID,
    SalesOrderNumber AS CurrentOrder,
    FIRST_VALUE(SalesOrderNumber)
        OVER(ORDER BY SalesOrderNumber
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW) AS FirstOrder,
    LAST_VALUE(SalesOrderNumber)
        OVER(ORDER BY SalesOrderNumber
            ROWS BETWEEN CURRENT ROW
            AND UNBOUNDED FOLLOWING) AS LastOrder
FROM Sales.SalesOrderHeader
ORDER BY OrderDate

当然，我们也可以使用分区来获取每个客户的第一个和最后一个订单。

SELECT
    SalesOrderID,
    CustomerID,
    SalesOrderNumber AS CurrentOrder,
    FIRST_VALUE(SalesOrderNumber)
        OVER(PARTITION BY CustomerID
            ORDER BY SalesOrderNumber
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW) AS FirstOrder,
    LAST_VALUE(SalesOrderNumber)
        OVER(PARTITION BY CustomerID
            ORDER BY SalesOrderNumber
            ROWS BETWEEN CURRENT ROW
            AND UNBOUNDED FOLLOWING) AS LastOrder
FROM Sales.SalesOrderHeader
ORDER BY OrderDate

您应该再次阅读关于框架的部分，并将其应用于 FIRST_VALUE 和 LAST_VALUE 函数。

14. 还没完……

在本文中，我们研究了使用单个 SELECT 语句可以做到的所有（或者至少很多）事情。这是本文的第一部分。第二部分仍在构建中，它将解释如何使用多个 SELECT 语句但仍然返回单个结果集。此外，它还将展示如何操作数据以供查看和过滤。

至少我希望到目前为止一切都清晰且阅读愉快。我很乐意回答任何问题或评论（好的和坏的，但我更喜欢好的）。希望您会回来阅读下一部分，查询 SQL Server 2012：第 II 部分！ 

祝您编码愉快！