Stairway to Advanced T-SQL Level 1: Intro to Advanced T-SQL Using a CROSS JOIN第九周翻译

原文出处：http://www.sqlservercentral.com/articles/Stairway+Series/119933/

本系列文章是楼梯系列的一部分：先进的T-SQL的楼梯，这个楼梯将包含一系列文章，将扩展到T-SQL基金会，你在前两个T-SQL楼梯，楼梯到T-SQL DML和T-SQL之外的基础知识。这个楼梯应该帮助读者准备通过微软认证考试74-461：查询微软SQL Server 2012。

这是一个新的楼梯系列文章中的第一篇文章，它将探讨Transact-SQL（TSQL）的更高级的特性。这个楼梯将包含一系列的文章，这些文章将在前面两个TSQL楼梯上学到的TSQL基础上扩展：

T-SQL DML的阶梯

通往T-SQL的楼梯:超越基础

这个“高级Transact-SQL”楼梯将涵盖以下TSQL主题:

使用交叉连接算子

使用Apple算子

理解常用表表达式（CTE）

使用Transact-SQL光标进行记录级处理

利用枢轴旋转数据

使用UNPIVOT将列转换为行

使用排序函数排序数据

用函数管理日期和时间

理解过句的变化

这个楼梯的读者应该已经很好地理解了如何查询、更新、插入和删除SQL Server表中的数据。此外，他们应该有一个工作方法的知识，可以用来控制他们的TSQL代码的流动，以及能够测试和操作数据。

这个楼梯应该帮助读者准备通过微软认证考试74-461：查询微软SQL Server 2012。

对于新的楼梯系列的第一部分，我将讨论交叉连接运算符。

CROSS JOIN简介：

CROSS JOIN操作符用于把一个数据集中的数据和另外一个数据集中的数据组合在一起。使用CROSS JOIN组合两个数据集的结果也称为笛卡尔积。 举个最简单的CROSS JOIN例子：

SELECT * FROM A CROSS JOIN B

注意当使用CROSS JOIN是，不需要像INNER /OUTER JOIN那样在两表之间加上连接字段，即ON 子句。

另外需要注意的是使用CROSS JOIN可以产生出非常大的数据集。比如上面例子中的A表有10行，B表有3行，那么两表CROSS JOIN之后的结果就有30行。如果A表有1000万行，B表有300万行，那么会有30000000000000行，即30万亿行。这可能会消耗掉SQL Server所有资源用于产生结果集，所以在使用CROSS JOIN是要小心，避免产生不必要的数据。

下面用一些例子来介绍一下CROSS JOIN。

使用CROSS JOIN的基本例子：

在这个例子中，我们关联两个简单的表，下面是脚本。请确认脚本运行在TempDB中（注：原文也要求不在master库中创建的，但是基于各种考虑，本人建议放在TempDB中可以通过重新启动SQL Server服务把操作还原，不影响其他库的操作）。

CREATE TABLE Product (ID int, 

                      ProductName varchar(100),

                      Cost money);CREATE TABLE SalesItem (ID int, 

                        SalesDate datetime, 

                        ProductID int, 

                        Qty int, 

                        TotalSalesAmt money);INSERT INTO Product

VALUES (1,'Widget',21.99),

      (2,'Thingamajig',5.38), 

  (3,'Watchamacallit',1.96);INSERT INTO SalesItem

VALUES (1,'2014-10-1',1,1,21.99),

      (2,'2014-10-2',3,1,1.96),

      (3,'2014-10-3',3,10,19.60),

      (4,'2014-10-3',1,2,43.98),

      (5,'2014-10-3',1,2,43.98); 

 

案例1：简单表的CROSS JOIN

 

对于第一个交叉连接示例，我将运行清单2中的代码。

SELECT * FROM 

Product CROSS JOIN SalesItem;

 

案例2：简单交叉连接示例

当我在SQL Server Management Studio窗口中运行清单2中的代码时，用我的会话设置来输出文本中的结果，我得到报表1中的输出：

ID  ProductName           Cost     ID   SalesDate               ProductID Qty  TotalSalesAmt

--- --------------------- -------- ---- ----------------------- --------- ---- ---------------

1    Widget               21.99    1    2014-10-01 00:00:00.000 1         1    21.99

1    Widget               21.99    2    2014-10-02 00:00:00.000 3         1    1.96

1    Widget               21.99    3    2014-10-03 00:00:00.000 3         10   19.60

1    Widget               21.99    4    2014-10-03 00:00:00.000 1         2    43.98

1    Widget               21.99    5    2014-10-03 00:00:00.000 1         2    43.98

2    Thingamajig          5.38     1    2014-10-01 00:00:00.000 1         1    21.99

2    Thingamajig          5.38     2    2014-10-02 00:00:00.000 3         1    1.96

2    Thingamajig          5.38     3    2014-10-03 00:00:00.000 3         10   19.60

2    Thingamajig          5.38     4    2014-10-03 00:00:00.000 1         2    43.98

2    Thingamajig          5.38     5    2014-10-03 00:00:00.000 1         2    43.98

3    Watchamacallit       1.96     1    2014-10-01 00:00:00.000 1         1    21.99

3    Watchamacallit       1.96     2    2014-10-02 00:00:00.000 3         1    1.96

3    Watchamacallit       1.96     3    2014-10-03 00:00:00.000 3         10   19.60

3    Watchamacallit       1.96     4    2014-10-03 00:00:00.000 1         2    43.98

3    Watchamacallit       1.96     5    2014-10-03 00:00:00.000 1         2    43.98

 

报表1：运行案例2时的结果

 

如果你查看报告1中的结果，你可以看到有15种不同的记录。这些前5个记录包含来自产品表的第一行的列值，与SaleTI目表中的5个不同行连接。对于产品表的2秒和3行也是如此。返回的行总数是Product表中的行数乘以SalestItTABLE表中行的行数，这是15行。

创建笛卡尔产品的一个原因可能是有用的，就是生成测试数据。假设我想使用我的产品和SaltItIt表中的日期生成许多不同的产品。我可以使用交叉连接，如案例3所做的那样：

SELECT ROW_NUMBER() OVER(ORDER BY ProductName DESC) AS ID,

       Product.ProductName

  + CAST(SalesItem.ID as varchar(2)) AS ProductName, 

       (Product.Cost / SalesItem.ID) * 100 AS CostFROM Product CROSS JOIN SalesItem;

 

案例3：简单交叉连接示例

 

当我运行案例3中的代码时，我得到了报表2中的输出。

ID    ProductName                                                 Cost

----- ----------------------------------------------------------- ---------------------

1     Widget1                                                     2199.00

2     Widget2                                                     1099.50

3     Widget3                                                     733.00

4     Widget4                                                     549.75

5     Widget5                                                     439.80

6     Watchamacallit1                                             196.00

7     Watchamacallit2                                             98.00

8     Watchamacallit3                                             65.33

9     Watchamacallit4                                             49.00

10    Watchamacallit5                                             39.20

11    Thingamajig1                                                538.00

12    Thingamajig2                                                269.00

13    Thingamajig3                                                179.33

14    Thingamajig4                                                134.50

15    Thingamajig5                                                107.60

 

报表2：运行案例3时的结果

 

正如您所看到的，通过查看清单3中的代码，我生成了许多行，这些行包含与我的产品表中的数据类似的数据。通过使用ROW_NUMBER函数，我能够在每一行上生成唯一的ID列。此外，我使用了SalesItem表中的ID列来创建唯一的产品名称和成本列值。生成的行数等于乘积表中的行数乘以SaleItem表中的行数。

本节中的示例仅在两个表之间执行交叉连接。可以使用交叉连接运算符跨多个表执行交叉连接操作。案例4中的示例在三个表中创建笛卡尔积。

SELECT * FROM sys.tables CROSS JOIN sys.objectsCROSS JOIN sys.sysusers;

 

案例4：使用交叉连接运算符创建三个表的笛卡尔积

运行案例4的输出有两个不同的交叉连接操作。由该代码创建的笛卡尔积将产生一个结果集，该集合将具有与sys.tables表中的行数相等的总行计数，同时，sys.objects中的行数乘以sys.sysusers中的行数。

 

当交叉连接像内部连接一样执行时

 

在前一节中，我提到当使用交叉连接运算符时，它会产生笛卡尔积。这不是一直都是真的。当使用WHERE子句限制交叉连接操作中涉及的表的连接时，SQLServer不创建笛卡尔积。相反，它的功能类似于正常的连接操作。为了演示这种行为，请查看案例5中的代码。

SELECT * FROM Product P CROSS JOIN SalesItem SWHERE P.ID = S.ProductID;

SELECT * FROM Product P INNER JOIN SalesItem SON P.ID = S.ProductID;

 

案例5：等价的两个SELECT语句

 

案例5中的代码包含两个SELECT语句。第一个SELECT语句使用交叉连接运算符，然后使用WHERE子句定义如何加入交叉连接操作中涉及的两个表。第二个SELECT语句使用一个带有ON子句的正常内部联接运算符来加入这两个表。SQL Server的查询优化器足够聪明，可以知道清单5中的第一个SELECT语句可以被重新写入内部连接。优化器知道，当交叉连接操作与WHERE子句一起使用时，可以重新编写查询，该WHERE子句在交叉连接中涉及的两个表之间提供连接谓词。因此，SQLServer引擎为案例5中的两个SELECT语句生成相同的执行计划。当不提供一个WHERE约束SQL Server不知道如何加入涉及交叉连接操作的两个表时，它在与交叉连接操作相关联的两个集合之间创建一个笛卡尔积。

 

使用交叉连接查找未售出的产品

 

前面几节中的例子帮助您理解交叉连接运算符以及如何使用它。使用交叉连接运算符的一个功能是使用它来帮助查找在另一个表中没有匹配记录的一个表中的项。例如，假设我想报告我的产品表中的每一个产品名称的总数量和总销售金额。因为在我的例子中，每一个产品名称都不是每天都有销售，所以我的报告要求意味着我需要显示一个数量0和总销售额0美元的产品，这些产品在某一天没有销售。这是交叉连接操作符与左外部连接操作一起帮助我识别那些在某一天没有销售的项目。满足这些报表要求的代码可以在案例6中找到：

 

SELECT S1.SalesDate, ProductName

     , ISNULL(Sum(S2.Qty),0) AS TotalQty

, ISNULL(SUM(S2.TotalSalesAmt),0) AS TotalSalesFROM Product PCROSS JOIN  (SELECT DISTINCT SalesDate FROM SalesItem

  ) S1LEFT OUTER JOIN 

SalesItem S2ON P.ID = S2.ProductIDAND S1.SalesDate = S2.SalesDate

GROUP BY S1.SalesDate, P.ProductNameORDER BY S1.SalesDate;

 

案例6：查找不使用交叉连接销售的产品

 

让我给你介绍一下这个密码。我创建一个子查询来选择所有不同的销售日期值。这个子查询给了我所有的销售日期。然后我与我的产品表交叉连接。这允许我在每个销售日期和每个产品行之间创建一个笛卡尔积。从交叉连接返回的集合将具有最终结果集中所需的每一个值，除了销售的每个产品的Qty和TotalSalesAmt的总和。为了获得这些汇总值，我对SaleItem表执行左外部连接，将其与与交叉连接操作创建的笛卡尔积一起加入。我根据ProductID和SaleDete列执行了这个连接。通过使用左外部连接，我的笛卡尔积中的每一行将被返回，并且如果有一个匹配的销售日期记录用于产品和销售日期，Qty和TotalSalesAmt值将与适当的行相关联。这个查询的最后一件事是使用GROUPBY子句总结基于销售日期和产品名称的Qty和TotalSalesAmount值。

 

性能考虑

 

CROSS JOIN操作由于可能产生笛卡儿积会存在一定的性能风险。因为SQL 引擎需要把两表的每一行都一一匹配并产生新数据，这个结果集可能非常巨大。如果一个有100万行的表与一个有10万行的表CROSS JOIN，结果集就是1000000*100000=100000000000 (1万亿！)。SQL Server需要花费大量的时间和资源去创建这个结果集。

但是CROSS JOIN又可以用于分析两个结果集的可能结果，比如每个月所有客户的所有销售情况，即使某些客户在某些月没有销售记录，也可以查询出对应的情况。当使用CROSS JOIN操作时，尽可能最小化CROSS JOIN的源数据和结果集，以便使性能可控。假设有一个表存放了最近2个月的销售记录，如果我需要生成一个关于哪些客户在一个月内完全没购买记录的报表时，就可以用CROSS JOIN。为了演示这一点，首先让我为1000个客户创建一组为期两个月的销售记录。我将使用案例7中的代码来实现这一点。

CREATE TABLE Cust (Id int, CustName varchar(20));CREATE TABLE Sales (Id int identity

                    ,CustID int

,SaleDate date

,SalesAmt money);SET NOCOUNT ON;DECLARE @I int = 0;DECLARE @Date date;WHILE @I < 1000BEGIN

SET @I = @I + 1;

SET @Date = DATEADD(mm, -2, '2014-11-01');

INSERT INTO Cust

VALUES (@I, 

       'Customer #' + right(cast(@I+100000 as varchar(6)),5));

WHILE @Date < '2014-11-01' 

BEGIN

IF @I%7 > 0

INSERT INTO Sales (CustID, SaleDate, SalesAmt) 

VALUES (@I, @Date, 10.00);

SET @Date = DATEADD(DD, 1, @Date);

END

 

案例7：TSQL创建用于性能测试的示例数据

 

案例7中的代码为1000个不同的客户创建了2个月的数据值。此代码不为每第七个客户添加销售数据。此代码产生1000个CUSTT表记录和52338个销售表记录。

 

案例9：针对不同的销售日期列表交叉连接

 

在案例8中，交叉连接运算符将1000个Cust记录与52338个销售记录相结合，以产生52338000行的记录集，然后用于确定在一个月内零销售的客户。在案例9中，我从销售表中更改了我的选择标准，只返回了一组不同的销售日期值。这个不同的集合只产生61个不同的SaleDete值，所以案例9中的交叉连接操作的结果只产生61000条记录。通过减少交叉连接操作的结果集，案例9中的查询在1秒内运行，而清单8中的代码在我的机器上运行19秒。这种性能差异的主要原因是SQLServer需要处理的记录卷对于每个查询执行的不同操作。如果查看两个清单的执行计划，您会发现计划略有不同。但是，如果您查看从嵌套循环（内部联接）操作生成的记录的估计数，在图形计划的右侧，您将看到案例8估计52338000个记录，而案例9中的相同操作仅估计61000个记录。从交叉连接嵌套循环操作生成案例8查询计划的这个大记录集然后传递给多个附加操作。因为案例8中的所有这些操作必须对5200万条记录进行操作。8比清单9要慢得多。

正如您所看到的，交叉连接操作中使用的记录数量会极大地影响查询运行的时间长度。因此，如果可以编写查询以最小化交叉连接操作中涉及的记录的数量，则查询将更有效地执行。

 

结论

 

交叉连接算子在两个记录集之间产生笛卡尔积。此运算符有助于识别一个表中没有另一个表中匹配记录的项。应该注意最小化交叉连接操作符使用的记录集的大小。通过确保交叉连接的结果集尽可能小，您将确保代码尽可能快地运行。

 

问答

 

在本节中，您可以通过回答以下问题来查看使用交叉连接运算符的理解程度。

问题1:

交叉连接运算符通过根据ON子句中指定的列匹配两个记录集来创建结果集。（真的还是假的）？

  真

假

问题2：

当表A和B包含重复行时，哪一个公式可以用来标识从两个表A和B之间的无约束交叉连接返回的行数？

表A中的行数、表B中行数

表A行数、表B中唯一行数

表A中唯一行数、表B中行数

表中唯一行数A、表B中唯一行数

问题3：

哪种方法提供了减少交叉连接操作产生的笛卡尔积的最佳机会？

确保所连接的两个集合尽可能多的行

以确保两个集合的连接尽可能少行

以确保交叉连接操作左侧的集合尽可能少的行

以确保交叉连接操作的右边的集合具有与P相同的行数

 

答案：

问题1：

正确的答案是B。交叉连接运算符不使用ON子句执行交叉连接操作。它将一个表中的每一行连接到另一个表中的每一行。交叉连接在加入两个集合时创建笛卡尔积。

问题2:

正确的答案是A.B、C和D是不正确的，因为如果在表A或B中有重复行，则在创建交叉连接操作的笛卡尔积时，每个重复行都被连接。

问题3：

正确的答案是B。通过减少交叉连接操作中涉及的两个集合的大小，最小化由交叉JOI操作创建的最终集的大小。C和D也有助于减少交叉连接操作创建的最终集的大小，但不如确保交叉连接操作中涉及的两个集合的行数最少。