翻译（10w）
先进的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的话题：
使用交叉连接运算符
使用应用操作符
理解常用表表达式（CTE）
使用Transact-SQL游标进行记录级处理
用枢轴转动侧边的数据
转向柱进行使用透视
使用排序函数排序数据
用函数管理日期和时间
理解从句的变化
这个楼梯的读者应该已经对如何从SQL Server表中查询、更新、插入和删除数据已经有了很好的理解。此外，他们应该有一个方法可以用来控制他们的TSQL代码流的工作知识，也能够测试和操作数据。
这楼梯应该帮助读者准备通过微软认证考试70-461：查询微软SQL Server 2012。
对于这个新的楼梯系列的第一部分，我将讨论交叉连接运算符。
交叉连接运算符介绍
交叉连接操作符可用于将一个数据集中的所有记录组合到另一个数据集中的所有记录。通过使用两组记录之间的交叉连接运算符，您将创建一个笛卡尔乘积。
下面是一个简单示例，使用交叉连接运算符连接两个表A和B：
|
SELECT * FROM A CROSS JOIN 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条记录包含该列的值从产品表的第一行中的5行加入不同SALESITEM表。对于产品表的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 Cost FROM 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表创建独特的产品名称，和成本列值。产生的行数等于在产品表次数在SALESITEM表行的行数。
本节中的示例仅在两个表上执行交叉连接。可以使用交叉连接运算符在多个表上执行交叉连接操作。清单4中的示例在三个表中创建笛卡尔乘积。
|
SELECT * FROM sys.tables CROSS JOIN sys.objects CROSS JOIN sys.sysusers; |
清单4：使用交叉连接操作符创建三个表的笛卡尔积
运行清单4中有两个不同的cross_join操作的输出。笛卡尔积这样创建的代码将产生一个结果集，将有一个总的行数等于在数量的行数sys.sysusers sys.objects倍sys.tables倍行的行数。
当交叉连接像内部连接那样执行时
在前面一节中，我提到当使用交叉连接操作符时，它将生成笛卡尔乘积。这不是真的。当您使用一个WHERE子句来约束交叉连接操作中所涉及的表的连接时，SQLServer不会创建笛卡尔乘积。相反，它的功能类似于正常连接操作。为了演示这种行为，请查看清单5中的代码。
|
SELECT * FROM Product P CROSS JOIN SalesItem S WHERE P.ID = S.ProductID; SELECT * FROM Product P INNER JOIN SalesItem S ON P.ID = S.ProductID; |
清单5：两个等价的SELECT语句。
清单5中的代码包含两个SELECT语句。第一个SELECT语句使用交叉连接运算符，然后使用WHERE子句来定义如何联接交叉联接操作中涉及的两个表。第二个SELECT语句使用一个普通的内部连接操作符和一个on子句来连接两个表。SQL Server的查询优化器非常聪明，可以知道清单5中的第一个SELECT语句可以重写为内部连接。优化器知道当交叉连接操作与WHERE子句一起使用时，它可以重新编写查询，该WHERE子句在交叉连接中涉及的两个表之间提供连接谓词。因此，SQL Server引擎为清单5中的SELECT语句生成相同的执行计划。当您不提供一个地方约束SQL Server不知道如何连接两个包含在交叉连接操作中的表时，它在与交叉连接操作相关的两个集合之间创建一个笛卡尔积。
使用交叉连接查找未售出的产品
前面几节中的示例帮助您理解交叉连接运算符以及如何使用它。使用交叉连接操作符的一个功能是使用它来帮助在一个表中查找与另一个表中没有匹配记录的项目。例如，假设我想在总量和在我的产品表的每个产品的总销售额为每一个日期，我的产品的任何一项销售报告。因为在我的例子中每个产品名称是不出售的每一天都有销售，我的报告要求，意味着我需要显示数量的0和总的产品没有在某一天卖了0美元的销售额。在这里，交叉连接操作符和左外联接操作将帮助我识别那些在某一天未售出的项目。满足这些报告要求的代码可以在清单6中找到：
|
SELECT S1.SalesDate, ProductName , ISNULL(Sum(S2.Qty),0) AS TotalQty , ISNULL(SUM(S2.TotalSalesAmt),0) AS TotalSales FROM Product P CROSS JOIN ( SELECT DISTINCT SalesDate FROM SalesItem ) S1 LEFT OUTER JOIN SalesItem S2 ON P.ID = S2.ProductID AND S1.SalesDate = S2.SalesDate GROUP BY S1.SalesDate, P.ProductName ORDER BY S1.SalesDate; |
清单6：查找未使用交叉连接销售的产品
让我来帮你看看这个代码。我创建一个查询，选择“销售日期”的独特价值。这个查询给我所有的日期，有个卖。然后我和我的产品表交叉连接。这让我创造每一个“销售日期”和每个产品排之间的笛卡尔积。设置返回从交叉连接将有价值的设置除了每个产品销售的数量和totalsalesamt和最终的结果，我需要。把那些汇总值我执行左外部联接对SALESITEM表连接它与笛卡尔积我创建了交叉连接操作。我完成了这个基础上加入ProductID和“销售日期”栏。通过使用左外连接的每一行中我的笛卡尔积将返回如果有一个匹配的ProductID和“销售日期”“销售日期”的记录，数量和totalsalesamt值将与相应的行副。此查询所做的最后一件事是通过条款使用组总结的数量和totalsalesamount基于“销售日期”和“产品名称”。
性能考虑
产生笛卡尔积的交叉连接运算符需要考虑一些性能方面。因为SQL引擎需要将一行集合中的每一行与另一组中的每一行连接起来，结果集可能相当大。如果我交叉连接一个有1000000行的表，而另一个表有100000行，那么我的结果集将有1000000×100000行，或者100000000000行。这是一个大的结果集，它将花费大量的时间来创建SQL Server。
交叉连接操作符可以是一个很好的解决方案，用于识别所有两个可能的组合的结果集，比如每个月所有客户的销售，甚至几个月内一些客户没有销售。当使用交叉连接操作符时，您应该尽量减少交叉连接的集合的大小，如果您想优化性能的话。例如，假设我有一个包含最近2个月销售数据的表。如果我想生成一个报告，显示了一个月没有销售的客户，那么确定一个月天数的方法可以极大地改变我的查询的性能。为了证明这一点，让我先为两个月内的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 < 1000 BEGIN 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 END |
清单7：TSQL为性能测试创建示例数据
清单7中的代码为1000个不同的客户创建了2个月的数据。此代码不会为每第七个客户添加销售数据。此代码生成1000个cust表记录和销售记录表52338。
为了演示如何使用交叉连接操作符，根据交叉联接输入集中使用的集合的大小不同，让我运行清单8和清单9中的代码。对于每个测试，我将记录返回结果所需的时间。
|
SELECT CONVERT(CHAR(6),S1.SaleDate,112) AS SalesMonth, C.CustName, ISNULL(SUM(S2.SalesAmt),0) AS TotalSales FROM Cust C CROSS JOIN ( SELECT SaleDate FROM Sales ) AS S1 LEFT OUTER JOIN Sales S2 ON C.ID = S2.CustID AND S1.SaleDate = S2.SaleDate GROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName HAVING ISNULL(SUM(S2.SalesAmt),0) = 0 ORDER 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 TotalSales FROM Cust C CROSS JOIN ( SELECT DISTINCT SaleDate FROM Sales ) AS S1 LEFT OUTER JOIN Sales S2 ON C.ID = S2.CustID AND S1.SaleDate = S2.SaleDate GROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName HAVING ISNULL(SUM(S2.SalesAmt),0) = 0 ORDER BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName |
清单9：针对不同的销售日期列表交叉连接
清单8中的交叉连接运营商加入1000客户记录52338的销售记录，生产记录集52338000行，这是用来确定谁曾在一个月零销售的客户。在清单9中，我改变了我的选择标准，从我的销售表只返回一组不同的“销售日期”值。这组不同只产生61种不同的“销售日期”值使十字架的结果加入运行清单9中只产生61000的记录。通过减少交叉连接操作的结果集，清单9中的查询在不到1秒的时间内运行，而清单8中的代码在我的机器上运行了19秒。造成这种性能差异的主要原因是SQL Server需要对每个查询执行的不同操作处理大量的记录。如果您查看两个清单的执行计划，就会发现这些计划略有不同。但是，如果您查看嵌套的循环（内部连接）操作生成的估计数，在图形计划的右侧，您将看到清单8估计了52338000条记录，而清单9中相同的操作只估计了61000条记录。这个大记录集，从交叉连接嵌套循环操作生成清单8的查询计划，然后传递给几个附加操作。因为清单8中的所有操作必须与5200万个记录相匹配。清单8比清单9慢得多。
正如您所看到的，交叉连接操作中使用的记录数量可以极大地影响查询运行的时间长度。因此，如果您可以编写查询以最小化交叉连接操作中所涉及的记录数量，则查询将更有效地执行。
结论
交叉连接运算符在两个记录集之间生成笛卡尔积。这个操作符有助于识别一个表中没有匹配记录的表中的项。应注意尽量减少交叉连接运算符使用的记录集的大小。通过确保交叉连接的结果集尽可能小，您将确保代码尽可能快地运行。
问题和答案
在本节中，您可以通过回答以下问题来检查使用交叉连接操作符的理解程度。
问题1：
交叉连接操作符通过根据子句中指定的列匹配两个记录集来创建一个结果集。（对还是错）？
真的
错误的
问题2：
当表A和B包含重复行时，可以使用哪一个公式识别从两个表A和B之间的无约束交叉连接返回的行数？
表中的行数为表B中行数的行数
表中的行数a是表B中唯一行数
表中的唯一行数a表B中的行数
表中的唯一行数a是表B中唯一行数
问题3：
哪种方法为减少交叉连接操作产生的笛卡尔积的大小提供了最好的机会？
确保所连接的两个集合有尽可能多的行。
确保所连接的两个集合尽可能少行。
确保交叉连接操作的左边设置尽可能少行。
确保交叉连接操作的右边设置尽可能少行。
答案:
问题1：
正确的答案是B。交叉连接操作符不使用on子句执行交叉连接操作。它将一个表中的每一行连接到另一个表中的每一行。交叉连接在加入两个集合时创建一个笛卡尔积。
问题2：
正确的答案是A，B，D和D是不正确的，因为如果表A或B中有重复的行，在创建交叉连接操作的笛卡尔乘积时，每个重复行都会被连接。
问题3：
正确答案是B.减少参与跨两集的大小最小化交叉连接操作的连接操作产生的最终集的大小。C和D也有助于减少交叉连接操作所创建的最终集的大小，但不如确保交叉连接操作中的两个集合行数最少。
这篇文章是楼梯部分高级T-SQL的楼梯
注册到我们的RSS提要并在我们发布一个新级别的楼梯时得到通知！RSS
本文链接:
http://www.sqlservercentral.com/articles/Stairway+Series/119933/
浙公网安备 33010602011771号