该系列 这篇文章是楼梯系列的一部分:高级T-SQL的阶梯。 这个楼梯将包含一系列的文章，这些文章将扩展到您在前面的两个T-SQL楼梯、T-SQL DML和T-SQL的基础上学习的T-SQL基础上。这个楼梯应该帮助读者准备通过微软认证考试70-461:查询微软SQL Server 2012。 这是将探索Transact SQL (TSQL)更高级特性的新楼梯系列的第一篇文章。这个楼梯将包含一系列的文章，这些文章将扩展到您在前两个TSQL stairways中学习的TSQL基础上: Stairway to T-SQL DML Stairway to T-SQL: Beyond the Basics 这个“高级Transact SQL”楼梯将涵盖以下TSQL主题: 使用交叉连接操作符。 使用应用操作符 理解公共表表达式(CTE) 使用Transact-SQL游标记录级别处理。 使用PIVOT将数据转向其一侧。 使用UNPIVOT将列变为行。 使用排序函数排序数据。 使用函数管理日期和时间。 了解以上条款的变化。 这个阶梯的读者应该已经很好地理解了如何从SQL Server表中查询、更新、插入和删除数据。此外，他们还应该掌握可以用来控制TSQL代码流的方法的工作知识，并且能够测试和操作数据。 这个楼梯应该帮助读者准备通过微软认证考试70-461:查询微软SQL Server 2012。 对于这个新的楼梯系列的第一部分，我将讨论交叉连接操作符。 交叉联接操作符介绍。 交叉连接操作符可以用来将一个数据集中的所有记录合并到另一个数据集中的所有记录中。 下面是一个使用交叉连接操作符来连接两个表a和B的简单示例: SELECT * FROM A CROSS JOIN B 注意，当使用交叉连接操作符时，没有连接子句连接两个表，就像在两个表之间执行内部和外部连接操作时使用的连接子句。 您需要注意的是，使用交叉连接可以生成一个大型记录集。为了探究这种行为，让我们看看两个不同的示例，看看结果集的大小如何来自于交叉连接操作。对于第一个示例，假设您是交叉连接两个表，其中表A有10行，表B有3行。交叉连接的结果集将是10乘以3或30行。对于第二个示例，假设表A有1,000万行，表B有300万行。在表a和B之间的交叉连接结果中有多少行?那将是一个巨大的30万亿的行。这是很多行，需要很多时间和大量的资源来创建这个结果集，所以在大型记录集上使用交叉连接操作符时需要非常小心。 让我们通过几个例子来进一步了解使用交叉连接操作符。
使用交叉连接的基本示例。 在前面的几个例子中，我们将连接两个示例表。清单1中的代码将用于创建这两个示例表。确保在用户数据数据库中运行这些脚本，而不是在master中运行。 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), 清单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 3 Watchamacallit 1.96 5 2014-10-03 00:00:00.000 1 报告1:运行清单2的结果。 如果您查看报告1中的结果，您可以看到有15个不同的记录。这些前5个记录包含产品表的第一行与SalesItem表中的5个不同行连接的列值。产品表的2秒和3行也是如此。返回的行数是Product表中的行数乘以SalesItem表中的行数，即15行。 创建Cartesian产品可能有用的一个原因是生成测试数据。假设我想在我的产品和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 报告2:运行清单3的结果。 通过查看清单3中的代码，您可以看到，我生成了许多行，其中包含与产品表中的数据类似的数据。通过使用ROW_NUMBER函数，我可以在每一行上生成唯一的ID列。另外，我使用SalesItem表中的ID列来创建惟一的ProductName和成本列值。产生的行数等于产品表中的行数乘以SalesItem表中的行数。 到目前为止，本节中的示例只执行了跨两个表的交叉连接。可以使用交叉连接操作符跨多个表执行交叉连接操作。清单4中的示例在三个表之间创建了一个Cartesian产品。 SELECT * FROM sys.tables CROSS JOIN sys.objects CROSS JOIN sys.sysusers; 清单4:使用交叉连接操作符创建三个表的Cartesian产品。 运行清单4的输出有两个不同的CROSS_JOIN操作。由该代码创建的Cartesian产品将产生一个结果集，该结果集将具有与sys中的行数相等的总行数。表乘以sys中的行数。对象乘以sysusers中的行数。 当交叉连接执行时，就像内部连接一样。 在上一节中，我提到过，当您使用一个交叉连接操作符时，它会产生一个笛卡尔积。这不是真的。当您使用WHERE子句约束连接到交叉连接操作中的表时，SQL Server不会创建Cartesian产品。相反，它的功能类似于正常的连接操作。为了演示此行为，请查看清单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不知道如何连接两个包含交叉连接操作的表时，它将在与交叉连接操作关联的两个集合之间创建一个Cartesian产品。 使用交叉连接查找未售出的产品。 前面几节中的示例将帮助您理解交叉连接操作符以及如何使用它。使用交叉连接操作符的一个功能是使用它来帮助在另一个表中查找没有匹配记录的表。例如，假设我想要在我的产品表中每一个产品的每一个产品被出售的日期，在我的产品表中报告每一个产品名称的总数量和总销售额。因为在我的例子中，每一个产品的名字都不是每天都有销售的，我的报告要求是指我需要显示一个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:查找不使用交叉连接销售的产品。 让我带你看看这段代码。我创建一个子查询来选择所有不同的SalesDate值。这个子查询给出了销售的所有日期。然后我将它与我的产品表连接起来。这允许我在每个SalesDate和每个产品行之间创建一个Cartesian产品。从交叉连接返回的集合将具有在最终结果集中所需要的所有值，除了每个产品的Qty和TotalSalesAmt之和。为了得到这些汇总值，我在SalesItem表上执行了一个左外连接，并将其与通过交叉连接操作创建的Cartesian产品连接起来。我基于ProductID和SalesDate列执行了这个连接。通过使用左外连接，我将返回笛卡尔产品中的每一行，如果有一个与ProductID和SalesDate相匹配的SalesDate记录，那么Qty和TotalSalesAmt值将与适当的行相关联。这个查询最后做的事情是使用GROUP BY子句来总结基于SalesDate和ProductName的Qty和TotalSalesAmount。 性能考虑 产生笛卡尔积的交叉连接运算符有一些性能方面需要考虑。因为SQL引擎需要将每一行与另一个集合中的每一行连接起来，因此结果集可以相当大。如果我做一个交叉连接，一个有1,000,000行的表，另一个表有100,000行，那么我的结果集将有1,000,000 X 100,000行，或100,000,000,000行。这是一个很大的结果集，它将花费大量的时间来创建它。 交叉连接操作符可以是一个很好的解决方案，可以在所有可能的组合中确定一个结果集，比如每个月的所有客户的所有销售，即使在几个月的时间内一些客户没有销售。在使用交叉连接操作符时，如果希望优化性能，应该尽量减少交叉连接的大小。例如，假设我有一个表，其中包含了过去两个月的销售数据。如果我想要生成一个报告，显示一个月没有任何销售的客户，那么确定一个月的天数的方法可以极大地改变我的查询的性能。为了证明这一点，我首先为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); END END清单7:TSQL为性能测试创建示例数据。 清单7中的代码为1000个不同的客户创建了两个月的数据。这段代码没有为每7个客户增加销售数据。此代码生成1000个Cust表记录和52338个Sales表记录。 为了演示如何使用交叉连接操作符执行不同的操作，这取决于在交叉连接输入集中使用的集合的大小，让我来运行清单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.SaleD,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中，CROSS JOIN操作符连接1000个Cust记录，其中有52,338个销售记录，生成一个记录集，记录为52,338,000个行，然后用来确定一个月销售为零的客户。在清单9中，我从Sales表中更改了选择标准，只返回一组不同的SalesDate值。这个不同的集合只产生了61个不同的SalesDate值，因此清单9中的CROSS JOIN操作的结果只产生了61,000条记录。通过减少交叉连接操作的结果集，清单9中的查询运行不到1秒，而清单8中的代码在我的机器上运行19秒。这种性能差异的主要原因是记录SQL Server需要为每个查询执行的不同操作的数量。如果您查看两个清单的执行计划，您将看到计划略有不同。但是，如果查看由嵌套循环(内连接)操作生成的记录数量，在图形化计划的右侧，您将看到清单8估计有52,338000条记录，而清单9中的相同操作只估计了61,000条记录。清单8的查询计划从交叉连接嵌套循环操作生成的这个大记录集，然后传递给多个额外的操作。因为清单8中的所有操作都必须处理5200万条记录。清单8比清单9慢得多。 正如您所看到的，交叉连接操作中使用的记录数可以极大地影响查询运行的时间长度。因此，如果您可以编写查询来最小化交叉连接操作中涉及的记录的数量，那么您的查询将执行得更有效率。 结论 交叉连接运算符在两个记录集之间产生一个笛卡尔积。这个操作符有助于在另一个表中不具有匹配记录的表中识别项。应注意尽量减小与交叉连接操作符使用的记录集的大小。通过确保交叉连接的结果集尽可能小，您将确保代码尽可能快地运行。 问题和答案 在本节中，您可以通过回答下列问题来回顾您如何使用交叉连接操作符理解您的理解。 问题1: 交叉连接操作符根据on子句中指定的列匹配两个记录集，从而创建一个结果集。 (TRUE or FALSE)? TRUE FALSE 问题2: 当表A和B包含重复行时，可以使用哪个公式来识别从两个表A和B之间不受约束的交叉连接返回的行数? 表A中的行数乘以表B中的行数。 表A中的行数乘以表B中唯一行数。 表A中唯一行数乘以表B中的行数。 表A中唯一行数乘以表B中唯一行数。 问题3: 哪种方法提供了减少交叉连接操作产生的笛卡尔积的最大机会? 确保连接的两个集合有尽可能多的行。 确保连接的两个集合尽可能少。 确保在交叉联接操作的左侧设置的行数尽可能少。 确保在交叉连接操作的右侧设置的行数越少越好。 答案: 问题1: 正确的答案是b。交叉连接操作符不使用ON子句来执行交叉连接操作。它将一个表中的每一行连接到另一个表中的每一行。当它连接两个集合时，交叉连接创建一个笛卡尔积。 问题2: 正确的答案是A . b、c和d是不正确的，因为如果表A或b中有重复的行，那么在创建交叉连接操作的Cartesian产品时，将会连接到每个重复的行。 问题3: 正确的答案是b。通过减少交叉连接操作中所涉及的两个集合的大小，可以最小化由CROSS JOI操作创建的最终集的大小。c和d还有助于减小交叉连接操作所创建的最终集的大小，但不像确保交叉连接操作中涉及的两个集合具有最少的行数一样最优。
