SQL SERVER 2008 编程入门经典读书笔记 -- SQL Server 复杂查询

1. 子查询的概念

子查询是嵌套在另一个查询中的普通 T-SQL 查询，在有一个 SELECT 语句作为另一个费查询的部分数据或条件的基础时，通过括号创建子查询。

子查询通常用于满足下列某个需求。

• 将一个查询分解为一系列的逻辑步骤
• 提供一个列表作为 WHERE 子句和 [IN | EXISTS | ANY | ALL ] 的目标
• 为父查询中的每个记录提供一个查询表

值得注意的是，大部分子查询可以使用连接来编写。在可以使用连接的地方，由于很多原因连接是首选的方式。

1.1 构建嵌套子查询

嵌套子查询只在一个方向嵌套 -- 返回在。如果希望返回一个列表，外部查询中使用的单个值，或者与 IN 运算符一起使用的一个完整的值列表。在需要使用一个显示的的 “=” 运算符时，将使用一个返回单个值的查询 -- 这就是一行中的一列。那么需要再外部查询中使用 IN 运算符。

从不严格意思上说，查询语法看起来像下面的两个语法模板：

        SELECT <SELECT list>FROM <SomeTable>WHERE <SomeColumn> = (SELECT <single column>FROM <SomeTable>WHERE <condition that results in only one row returned>)

或者：

SELECT <SELECT list>FROM <SomeTable>WHERE <SomeColumn> IN (SELECT <single column>FROM <SomeTable>WHERE <condition>)

显然，确切的语法会有所变化，不仅因为需要替换选择列表和确切的表名称，而且因为在内部查询和/或外部查询中有一个多表连接。

1.1.1 使用返回多个值的 SELECT 语句的嵌套查询

下面通过一个显式的示例来了解问题的本质。例如，假设希望知道第一天通过系统销售的产品的每个条目的 ProductID (注意 OrderDate 为 Date 类型)

SELECT DISTINCT soh.OrderDate, sod.ProductIDFROM Sales.SalesOrderHeader sodJOIN Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderIDWHERE OrderDate = (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader)

1.1.2 使用返回多个值的子查询的嵌套查询

也许，最常见的一种子查询是检索某些形式的域列表，然后用作另一个查询的条件。

下面通过 查询具有折扣信息的所有产品的列表 的例子来进行了解。

SELECT ProductID, NameFROM Production.ProductWHERE ProductID IN (SELECT ProductID FROM Sales.SpecialOfferProduct ):)

尽管这种工作方式很好，但更好的方式是使用内部连接实现，而不是使用嵌套的 SELECT。例如，可以通过允许简单的连接来得到同于的结果。

SELECT DISTINCT pp.ProductID, NameFROM Production.Product PPJOIN Sales.SpecialOfferProduction ssop on pp.ProductID = ssop.ProductID;

由于性能方面的考虑，如果没有特别的理由使用嵌套的 SELECT 的话，则还应该使用连接方法作为默认解决方案。

提示：

SQL SERVER 可以灵活处理这类问题。在大部分情况下，SQL SERVER 实际上会将嵌套子查询方法解析为

和使用连接一样的查询计划--事实上，如果检查嵌套子查询和前面的连接查询计划，就会发现他们是完全相同

的计划。因此，在大多数情况下，这两种方法是没多大区别的。当然，这里所说的是“大多数情况”。当查询计划

不同时，连接通常是更好的选择，因此推荐在默认情况下使用连接的语法

1.1.3 使用嵌套的 SELECT 发现孤立的记录

这种嵌套的 SELECT 和前面的示例几乎相同，处理添加 NOT 运算符。这是得在转换连接语法时设置为等同于

外部连接而不是内部连接。

下面看一下所有没有匹配商品的折扣记录：

SELECT DescriptionFROM Sales.SpecialOffer ssoWHERE sso.SpecialOfferID != 1AND sso.SpecialOfferID NOT IN (SELECT SpecialOfferID FROM Sales.SpecialOfferProduct);

上面的功能也可以用外连接实现

SELECT DescriptionFROM Sales.SpecialOfferProduct ssopRIGHT OUTER JOIN Sales.SpecialOffer sso on sso.SpecialOfferID = ssop.SpecialOfferIDWHERE sso.SpecialOfferID != 1 AND sso.SpecialOfferID IS NULL;

2 关联子查询

2.1 关联子查询的工作原理

关联子查询和上面的嵌套子查询的不同之处在于信息传递是双向的，而不是单向的。在嵌套在查询中，内部查询只处理了一次，然后将信息传递到外部查询。

而外部查询也只执行一次--提供和您自己可能输入的完全相同的值或列表。

而在关联子查询中，内部查询用2外部查询提供的信息允许，反之亦然。这似乎令人有些困惑，但是这是一个分3个步骤进行的·处理过程。

（1）外部查询获得一个记录，然后将该记录传递到内部查询

（2）内部查询根据传递的值执行

（3）然后内部查询将结果值传回外部查询，而外部查询利用这些值完成处理过程

2.2 在 WHERE 子句中的关联子查询

对于上面查询系统中第一天下单的客户信息，考虑查询系统中每个客户第一天下单的日期。这里就需要

一点技巧。当使用嵌套子查询时，只在整个系统中查询第一天--现在需要查找每个客户第一天下订单

的日期。如果使用两个单独的查询来完成，可以创建一个临时表，然后与之进行连接。

使用临时表的解决方案有点类似如下所示：

USE AdventureWorks2008;-- Get a list of customers and the date of their first orderSELECT soh.CustomerID, MIN(soh.OrderDate)AS OrderDateINTO #MinOrderSates>FROM Sales.SalesOrderHeader sohGROUP BY soh.CustomerID-- Do something additional with that informationSELECT soh.CustomerID, soh.SalesOrderID, soh.OrderDateFROM Sales.SalesOrderHeader sohJOIN #MinOrderDates t on soh.CustomerID = t.CustomerID  and soh.OrderDate = t.OrderDate ORDER BY soh.CustomerID  DROP TABLE #MinOrderDates;

有时，在不使用游标的情况下，使用多查询的方法是唯一的方法--不过这里并不是这样的。

如果希望在一个查询中国允许，那么需要找到一种方法来查看每个用户。可以让内部查询基于当前外部查询的 CustomerID 来查找。然后需要

将返回值传递给外部查询。这样可基于最早的订单日期进行匹配。

代码如下所示：

SELECT sob1.CustomerID, sob1.SalesOrderID, sob1.OrderDateFROM Sales.SalesOrderDetail soh1WHERE soh1.OrderDate = (SELECT Min(soh2.OrderDate) FROM Sales.SalesOrderDetail soh2WHERE soh2.CustomerID = soh1.CustomerID )ORDER BY CustomerID;

在这个特殊查询中，外部查询只在 WHERE 子句中引用内部查询--也可以在选择列表中包含来自内部查询的数据。

2.3 在 SELECT 列表中的关联子查询

子查询也可以用于在选择结果中提供不同的答案。当所要查找的信息与查询中饭的其他信息完全不同时，经常出现这种情况（例如，需要一个字段的

聚合结果，但是又不希望所有这些数据影响其他返回的字段）。

这里查询客户的账号和在哪天开始订购某物。代码如下所示：

SELECT sc.AccountNumber,(SELECT Min(OrderDate) FROM Sales.SalesOrderHeader soh  WHERE sob.CustomerID = sc.CustomerID) AS OrderDateFROM Sales.Customer sc;

3. MERGE 命令

MERGE 命令是SQL SERVER 2008中的新功能，它提供了一种不同的方式来使用 DML语句。通过 MERGE命令，可以将多个 DML动作语句（INSERT、UPDATE、DELETE）

组合成一个整体动作，从而提高性能（他们可共享许多相同的物理操作）和简化事物。MERGE 利用一个特殊的 USING 子句，该子句有点类似于 CTE.

然后，USING 子句中的结果可按条件应用于 INSERT、UPDATE 和 DELETE 语句，其基本语法如下所示：

下面采用一个收货库存的示例来说明。假定为了做报表，创建了一个特殊的销售汇总表。希望每日运行查询，将新的销售记录添加到每月汇总表中。

在某月的第一个晚上，几乎没什么工作可做，因为该月没有其他汇总记录，该天的销售记录也已汇总并插入了。但在第二天晚上，情况不同了，需要

像第一天那样汇总并插入新纪录，但（对于该月销售的产品）只需要更新已有记录。

下面看一下 MERGE 命令如何在一个步骤中同时管理两个动作。在这之前，需要创建汇总表：

USE AdventureWorks2008CREATE TABLE Sales.MonthlyRollup(  Year smallint NOT NULL, Month tinyint NOT NULL, ProductID int not null FOREIGN KEY REFERENCES Production.Product(ProductID), QtySold int NOT NULL, CONSTRAINT PKYearMonthProductID PRIMARY KEY(Year, Monh, ProductID) );

编写 MERGE 语句如下

MERGE Sales.MonthlyRollup AS smrUSING( SELECT soh.OrderDate, sod.ProductID, SUM(sod.OrderQty) AS QtySold FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail  sod on soh.SalesOrderID = sod.SalesOrderID WHERE soh.OrderDate >= '2003-08-22' AND soh.OrderDate < '2003-08-23' GROUP BY soh.OrderDate, sod.ProductID) AS sON (s.ProductID = smr.ProductID)WHEN MATCHED THENUPDATE SET smt.OtySold = smr.QtySold + s.QtySold WHEN NOT MATCHED THENINSERT (Year, Month, ProductID, QtySold)VALUES (DATEPART(yy, s.OrderDate),DATEPART(m, s.OrderDate),s.ProductID,s.QtySold);