第十周翻译

先进的T-SQL 1级楼梯：高级T-SQL使用交叉连接的介绍

通过 Gregory Larsen， 2016 / 02 / 19 （首次公布：2014 / 12 / 17）

该系列

本文是系列的一部分：楼梯楼梯 高级T-SQL

这楼梯将包含一系列的文章，将扩大在T-SQL基础你在前两T-SQL楼梯了，楼梯T-SQL DML和T-SQL基础之外。这楼梯应该帮助读者准备通过微软认证考试70-461：查询微软SQL Server 2012。

这是第一篇文章在一个新的楼梯系列将探索更先进的特性，Transact-SQL（TSQL）。这楼梯将包含一系列的文章，将扩大在TSQL基础你在前两TSQL楼梯了：

·楼梯T-SQL DML

·楼梯T-SQL：超越基础

这个“先进的Transact-SQL的阶梯》将涵盖以下TSQL的话题：

·采用交叉连接操作

·利用算子的应用

·了解公用表表达式（的）

·记录级处理使用Transact-SQL游标

·把数据在其一侧通过枢轴

·转向柱进行使用透视

·订购你的数据使用排名函数

·管理日期和时间函数

·了解过条款的变化

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

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

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

交叉连接运算符介绍

交叉连接操作符可用于将一个数据集中的所有记录组合到另一个数据集中的所有记录。通过使用两组记录之间的交叉连接运算符，您将创建一个笛卡尔乘积。

下面是一个简单示例，使用交叉连接运算符连接两个表A和B：

从交叉连接b中选择*

注意，当使用交叉连接操作符时，没有连接子句连接两个表，就像在两个表之间执行内部和外部连接操作时使用的连接子句一样。

您需要注意，使用交叉连接可以生成一个大的记录集。为了探究这种行为，让我们看看两个不同的例子：结果集从交叉连接操作中会产生多大的效果。对于第一个示例，假设您是交叉连接两个表，其中表A有10行，表B有3行。我设置一个交叉连接将10次3或30行。对于第二个例子，假设表A有1000万行，表B有300万行。在表A和B之间的交叉连接结果集中会有多少行？那将是惊人的30000000000000行。这是一个很大的行，它将花费大量的时间和大量资源来创建结果集。因此，在使用大型记录集上的交叉连接运算符时需要小心。

让我们仔细研究一下使用交叉连接操作符来探索几个示例。

使用交叉连接的基本示例

对于前两个示例，我们将加入两个示例表。清单1中的代码将用于创建这两个示例表。确保你运行这些脚本在用户数据库。

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：交叉连接的示例表

对于第一个交叉连接示例，我将运行清单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条记录包含列值的 产品 表的第一行中的 SALESITEM 表5不同行加入。同样的 产品 表2的第二和3行是真的。总返回的行数是在 产品 表次数在 SALESITEM 表行的行数，这是15行。

创建笛卡尔产品可能有用的一个原因是生成测试数据。假如我要生成多个不同的产品使用的日期在我的 产品 和 SALESITEM 表。我可以使用交叉连接来实现，如清单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 柱。另外，我用 ID 柱从我的 SALESITEM 表来创建独特的 ProductName，和 成本 列值。产生的行数等于在 产品 表次数在 SALESITEM 表行的行数。

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

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

清单4：使用交叉连接操作符创建三个表的笛卡尔积

运行清单4中有两个不同的cross_join操作的输出。笛卡尔积这样创建的代码将产生一个结果集，将有一个总的行数等于 系统中的行数。数在 系统行的表 倍。对象 次数排在 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子句在交叉连接中涉及的两个表之间提供连接谓词。因此，SQL Server引擎为清单5中的SELECT语句生成相同的执行计划。当您不提供一个地方约束SQL Server不知道如何连接两个包含在交叉连接操作中的表时，它在与交叉连接操作相关的两个集合之间创建一个笛卡尔积。

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

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

前面几节中的示例帮助您理解交叉连接运算符以及如何使用它。使用交叉连接操作符的一个功能是使用它来帮助在一个表中查找与另一个表中没有匹配记录的项目。例如，假设我想在总量和我 产品 表的每一 ProductName 总销售额为每一个日期，我的产品的任何一项销售报告。因为在我的例子中每个 ProductName 不卖每天都有销售，我的报告要求，意味着我需要显示数量的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.SalesDateGROUP BY S1.SalesDate, P.ProductNameORDER BY S1.SalesDate;

清单6:查找不使用交叉连接销售的产品

让我们看一下这段代码。我创建了一个子查询，它选择了所有不同的销售日期值。这个子查询提供了所有出售的日期。然后我把它和我的产品表连接在一起。这允许我在每个销售日期和每一个产品行之间创建一个笛卡尔产品。从交叉连接返回的集合将在最终结果集中拥有我所需要的每一个值，除了每个产品的Qty和totalsales amt的总和。为了获得这些汇总值，我执行了一个左外连接，与销售项目表一起使用我创建的带有交叉连接操作的笛卡尔产品。我基于ProductID和sales date列执行此连接。通过使用左边的外部连接，我的笛卡尔产品中的每一行都将返回，如果产品和销售日期有一个匹配的销售日期记录，那么Qty和totalsalesforce值将与适当的行相关联。这个查询的最后一件事情是使用GROUP BY子句来总结基于销售日期和产品名称的Qty和totalsalesforce数量。

性能考虑

产生笛卡尔产品的交叉连接操作符有一些性能方面需要考虑。因为SQL引擎需要将一个集合中的每一行与另一个集合中的每一行连接起来，因此结果集可能相当大。如果我做一个交叉连接一个表，它有1,000,000行，另一个表有100,000行，那么我的结果集将会有100万X 100,000行，或者1000亿行。这是一个很大的结果集，它将花费大量的时间来创建SQL Server。

交叉连接操作符可以是一个很好的解决方案，可以在所有可能的组合中识别结果集，比如每个月的所有客户的销售，甚至在几个月的时间里，一些客户没有销售。在使用交叉连接操作符时，如果要优化性能，就应该尽量减少交叉连接的大小。例如，假设我有一个表，其中包含了过去两个月的销售数据。如果我想要生成一个报告，它显示了一个月没有销售的客户，那么确定一个月的天数的方法可以极大地改变我的查询的性能。为了演示这一点，让我首先为1000个客户创建一个为期2个月的销售记录。我将使用清单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);

ENDEND

清单7:为性能测试创建示例数据的TSQL

清单7中的代码为1,000个不同的客户创建了两个月的数据。这段代码没有为每7个客户添加销售数据。该代码生成了1000个Cust表记录和523338个销售表记录。

为了演示如何使用交叉连接操作符进行不同的操作，这取决于交叉连接输入集所使用的集合的大小，让我运行清单8和清单9中的代码。对于每个测试，我将记录返回结果所需的时间。

为了演示如何使用交叉连接操作符进行不同的操作，这取决于交叉连接输入集所使用的集合的大小，让我运行清单8和清单9中的代码。对于每个测试，我将记录返回结果所需的时间。

SELECT CONVERT(CHAR(6),S1.SaleDate,112) AS SalesMonth, C.CustName, 

       ISNULL(SUM(S2.SalesAmt),0) AS TotalSalesFROM Cust CCROSS JOIN  (SELECT SaleDate FROM Sales ) AS S1LEFT OUTER JOIN 

Sales  S2ON C.ID = S2.CustIDAND S1.SaleDate = S2.SaleDateGROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustNameHAVING ISNULL(SUM(S2.SalesAmt),0) = 0ORDER BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName

清单8:针对所有销售记录的交叉连接

SELECT CONVERT(CHAR(6),S1.SaleDate,112) AS SalesMonth, C.CustName, 

       ISNULL(SUM(S2.SalesAmt),0) AS TotalSalesFROM Cust CCROSS JOIN  (SELECT DISTINCT SaleDate FROM Sales ) AS S1LEFT OUTER JOIN 

Sales  S2ON C.ID = S2.CustIDAND S1.SaleDate = S2.SaleDateGROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustNameHAVING ISNULL(SUM(S2.SalesAmt),0) = 0ORDER BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName

清单9:针对不同销售日期的交叉连接

在清单8中，CROSS JOIN操作符加入了1000个Cust记录，其中有52,338个销售记录，生成一个创纪录的52338000行的记录集，用此来确定一个月销售为零的客户。在清单9中，我将选择标准从Sales表中更改为只返回一组不同的SalesDate值。这个独特的集合只产生了61个不同的销售日期值，因此清单9中的CROSS JOIN操作的结果只产生了61,000条记录。通过减少交叉连接操作的结果集，清单9中的查询运行不到1秒，而清单8中的代码在我的机器上运行了19秒。这种性能差异的主要原因是记录SQL Server需要处理每个查询执行的不同操作的数量。如果您查看两个清单的执行计划，您将看到计划略有不同。但是，如果你在图形化计划的右侧看到嵌套循环(Inner Join)操作所生成的记录的数量，将看到清单8差不多有52338000条记录，而清单9中的操作仅有61,000条记录左右。这个巨大的记录集，清单8的查询计划从交叉连接嵌套循环操作中生成，然后再传递到几个额外的操作。因为清单8中的所有操作都必须处理5200万的记录。清单8比清单9慢得多。

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

结论

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

问题和答案

在本节中，您可以通过回答下列问题，来回顾您使用CROSS JOIN操作符理解的程度。

问题1:

交叉连接操作符根据on子句中指定的列匹配两个记录集创建一个结果集。(真或假)?

·*真

·*假

问题2:

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

·在表的行数乘以B表的行数

·在表的行数乘以B表独特的行数

·在表一个独特的行数乘以B表的行数

·在表一个独特的行数乘以B表独特的行数

问题3:

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

·确保两组参加尽可能多的行

·确保两组参加尽可能少的一样行

·确保将左边的交叉连接操作尽可能少的行

·确保交叉连接的设置为正确的操作尽可能少的行

答案:

问题1:

正确的答案是b . CROSS JOIN操作符不使用ON子句来执行交叉连接操作。它将一个表中的每一行连接到另一个表中的每一行。当它连接两个集合时，交叉联接创建一个笛卡尔乘积。

问题2:

正确的答案是A,b,c和d是不正确的,因为如果有重复的行表A或b复制的每一行中加入的笛卡儿积在创建交叉连接操作。

问题3:

正确的答案是b。通过减少大小的两组参与交叉连接操作最小化的最后一组的大小由交叉中操作。c和d也有助于减少最后一组的大小产生的交叉连接操作,但不像确保两组最优参与交叉连接的操作可能有最少的行。

本文是楼梯的一部分先进的t - sql楼梯

注册我们的RSS提要并尽快通知我们发布一个新的水平在楼梯!